Jun 25, 2015

Oracle Hints



1) Hint to use with Like clause

If your SQL contain a LIKE clause then you could consider using a hint on the column where an index exists on this column.

Use the /*+index(table index_name) */ hint. The LIKE clause may prevent the use of the index. If the index is physically smaller than the table then it will likely be faster to use the index rather than the table.

2)  APPEND

Append only works with Insert into table1 select * from table2;
It really doesn't make sense for a single-row insert



Jun 21, 2015

Apr 29, 2015

ORA-01555 : snapshot too old

ORA-01555 : snapshot too old : rollback segment number 60 with name

 This is because rollback records needed by a reader for consistent read are overwritten by other writers
This error normally occurs if you are running a very big transaction (like activating millions of records in an odds) and the redo log is to small. Try to enlarge the redo log and run your transaction again.

sql> show parameter undo_retention

 Increase Undo Table space to Auto Extended on.

1- increase the setting of UNDO_RETENTION:

 to change: SQL> alter system set undo_retention=(new-value)

2- increase the tablespace UNDO that contain the ROLLBACK segments

alter tablespace UNDO add datafile '(name-of-the-new-datafile)' size (size-value)M;

Crazy Query

 Do you have any idea how this queries are working  ?


 select * from user_details d where exists (select 1/0 from dual x where d.first_name =x.dummy) ;

 select * from user_details d where exists (select 1/0 from dual ) ;

 Query  to get HR, MI,SS
 

select trunc((sysdate-created)*24) "Hr",trunc(mod( (sysdate-created)*24*60,60)) "Mi",
 trunc( mod( (sysdate-created)*24*60*60,60 )) "Sec"
from all_users where rownum < 10 ;

ORA-01450: maximum key length

create table ir_temp_feeload (s_category varchar2(800),s_cov varchar2(800),v_rate varchar2(10) ) ;

 alter table ir_temp_feeload  add constraint uk_ unique(s_category,s_cov) ;

ORA-01450: maximum key length (6398) exceeded

This error is due to the bad database design
This error occurs as key length of index exceeds 6398

key length = sum of all column lengths in index + number of columns + 2 (key length) + 6 (restricted ROWID) + 1 (ROWID field length)


Here  = (800+800) + (3)+2+6+1 = 1612

select * from nls_database_parameters where parameter like '%SET%';

AL32UTF8 is a multi-byte character set with up to 4 bytes per character

So the key value =  1612*4 = 6448

 Solution :
---------

As a general rule, indexing on very large columns (raw, long, clob) is rarely useful because the optimizer will almost always find a full-table scan cheaper than invoking an index on a long column value.

It is better to create a look up table that holds all the distinct values for the large text with a number id. Then you have a foreign key in the main table or use it as primary key