Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance.SQL> drop table t;Table dropped.SQL> create table t as select * from all_objects;Table created.SQL> select count(*) from t;COUNT (*) ---------- 29120 SQL> set autotrace traceonly statistics;SQL> set arraysize 2SQL> select * from t;29120 rows selected.Statistics---------------------------------------------------------- 14779 consistent gets Note how one half...

Read More

Can we create table name and column name with case sensitive in oracle?

The table name and Column names CAN BE case sensitive in oracle, but are not by default. Bydefault, any way you create a table, upper case, lower case, mixed case, doesn't matter; everything will be forced to UPPER CASE. If you want it not to be UPPER CASE, you need to create the column with quotes around the name of the table or column and the case for the column that you specify will be kept.The below script explains that very well.The first one we see the table name with the case sensitive.SQL> create table "Test" ( num number);Table created.We...

Read More

Index skip scan

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8.The optimizer will allow to use the index since our predicate did not involve the column X. the optimizer would notice that it did not have to go to the table to get either X or Y. they are in the index. So it may very well option for the fast full index scan.See below link for more information:http://karthikeyanbaskaran.blogspot.com/2010/02/index-is-not-getting-used.htmlThe...

Read More

Index is not getting used

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8 and see how the optimizer executes.We see this in the below example. create table test (x number, y number);insert into test values ( 4,5);insert into test values (1,3);insert into test values (7,5);insert into test values (3,6);SQL> select * from test;X Y----- ---- 4 5 1 37 53 6SQL> create index test_idx on test(x,y);Index created.SQL> begin2 dbms_stats.gather_table_stats('SYSTEM','TEST');3...

Read More

Comparing the Contents of Two Tables

Comparing the Contents of Two Tables:I have two tables named M and N. They have identical columns and have the same number of rows via select count(*) from M and from N. However, the content in one of the rows is different, as shown in the following query: SQL> select * from M where C1=1;C1 C2 C3-- ------------ ----1 AAAAAAAAAAAA 100SQL> select * from N where C1=1;C1 C2 C3--- ------------ --------1 AAAAAAAAAAAB 100The only difference is the last character in column C2. It is an A in table M and a B in table N. I would like to write SQL to...

Read More

Index scan gives the sorted record ?

Index scan gives the sorted record. SQL> column plan_plus_exp format a100 SQL> set trimspool on SQL> create table t nologging as select * from dba_objects; Table created. SQL> set time on SQL> set timing on SQL> create index t_idx on t(owner,object_id) nologging compute statistics; Index created. Elapsed: 00:00:00.03 SQL> select count(*) from t; COUNT(*) ---------- 29515 Elapsed: 00:00:00.00 SQL Query 1: SQL> set autotrace on 18:07:31 SQL> select rownum' 'owner' 'object_id from t where owner='SYS' and rownum < 11;...

Read More