Oracle: Check for special character in SQL

select str, dump(str)
from test
where translate(upper(str),
CHR(0)||'QWERTYUIOPASDFGHJKLZXCVBNM ,.<>/?;:''"[{]}1234567890-=!@#$%^&*()_+\|`~',
CHR(0)
) IS NOT NULL

Asset Depreciation query

SELECT temp.*, (COST - acc_dcost) final_cost
FROM (SELECT asset.code, deprec.yr, COST, deprec.dcost dcost_year,
SUM (dcost) OVER (PARTITION BY deprec.code ORDER BY yr)

acc_dcost
FROM (SELECT 1003 code, 10000 COST
FROM DUAL) asset,
(SELECT 2001 yr, 1003 code, 163 dcost
FROM DUAL
UNION ALL
SELECT 2002, 1003, 161
FROM DUAL
UNION ALL
SELECT 2003, 1003, 158
FROM DUAL) deprec) temp

Selecting column for index

As you must be knowing, indexing the column can boost the performance by leaps and bounds. But you can achieve this only if you index proper column.

The index work as follows:

1. You index a column.

2. Database creates a mapping for each value of that column to where it can find it row.

3. There can be more than one row for given value (index collision,which is generally searched sequentially)

4. When you are looking for a row with particular value, database searches the index to find out where it can get the actual row for that value.

5. Then reads the actual data from physical location.

Now this process can degrade the performance if most column values are similar. In that cases collision occurs frequently and the overhead of the index outperforms its benefits. One way to find out a column for indexing is calculate the 'selectivity' for it. In the simplest term it is the % of distinct keys present in the column.

You can use following query to find out the no of different keys for a table;

SELECT   column_name, num_distinct,
         num_distinct / (SELECT COUNT (*)
                           FROM employee) AS SELECTIVITY
    FROM user_tab_columns
   WHERE LOWER (table_name) = 'employee'
ORDER BY num_distinct DESC

 

COLUMN_NAME	NUM_DISTINCT	SELECTIVITY
EMP_ID	52754	0.999526327
EMP_NAME	52753	0.99950738
EMP_FULLNAME	51964	0.984558252
EMP_FIRSTNAME	29898	0.566475303
EMP_LASTNAME	23749	0.449970632

The closer the selectivity to 1 , better is the column to index.

If you have to index column with low selectivity, try to use composite index ( having more than one column). This might lead to a good selectivity composite index. e.g If Employee table contains Department Id, with

low selective , it can be combined with the project Id to get better selectivity.

If you are trying to index a string column and your search criteria for it is like '%pattern%'; the index may be ignored by the database. In such case if you can change search criteria to '%pattern' (you are searching for the values that 'ends with' given pattern); then you can use reverse index.

 

 

 

 

 

 

Oracle - Hierarchical Queries

Is your database contains parent - child relationship ? If yes, then there is quite a chance that you have to write a query to get the complete Hierarchy given a node of the tree.

I found a very nice article at www.databasejournal.com.

http://www.databasejournal.com/features/oracle/article.php/3552521 

 

Hope this will help you !

 

Enjoy.

Ner Labs.

 

Does column Order Within Row affects the performance ?

Surely it does. After all at the end it boils down to file operations. and variable length columns placed in between definer hampers the performance. Also the databases like oracle does not required any space for the null values at the end of the row.

You can find the detailed discussion in the SQL Performance Tuning by Peter Gulutzan. Or you can read the extract Here

There are potential advantages to storing all nullable or variable values at the end of the row:

*

Oracle won't waste storage space on a NULL for a variable-length column at the end of a row.
*

Any DBMS can calculate the column's offset within the row more quickly if no variable-length columns are before it that have to be skipped.

You need not change your table definitions to realize these advantages if the DBMS will automatically figure out the best column order when you execute CREATE TABLE (as, for example, Microsoft does) or if the DBMS has different criteria (for example, with IBM there's a slight advantage in putting the most volatile column at the end; see the section "IBM Logging" in Chapter 14, "Data Changes"). So put columns in the order that users will expect to see them when they SELECT *. Typically, this means that related columns are grouped together, and the primary key is on the left, followed by columns that are frequently used for searches.

Here's another tip. One table with 50 columns is better than 50 tables with one column each because:

*

The per-column storage overhead is less than the per-row storage overhead.
*

The position of the fiftieth column might be determinable at parse time.
*

Fewer entries are in the system catalog.

Therefore, if you have a miscellaneous pile of N static (read-only) system values, store them horizontally (in one table) rather than vertically (in N tables). This switch to a horizontal rather than vertical viewpoint is sometimes called pivoting.
The Bottom Line: Column Order

Storing all nullable or variable values at the end of the row may have advantages, but you need not change your table definitions-some DBMSs will automatically figure out the best column order when you execute CREATE TABLE and other DBMSs have different criteria.

Recommendation: Put columns in the order that users will expect to see when they execute SELECT *: generally, related columns grouped together, and the primary key on the left, followed by columns that are frequently used for searches.