Index usage in depending on where clause changes.

10-11  Source: Network gathering  Views:13 

Advertisement
Hello Friends,
I need your help for one issue.
I have one query , which is using two table Say T1 and T2, where C1 is common column using which both are joined.
C1 is primary key in T1, but no index available in T2 for C1. T1C2 is the column which we want to select.
(Note that Either of table can be a Master table)
Now see the query:
Select T1C2
From T1, T2
where T2.C1 = T1.C1
Here where clause may have other conditions and From clause may have others tables as per requirements.
I want to know that, if, I change the query like following to let my query use the available index of T1.C1.
Select T1C2
from T1, T2
where T1.C1 = T2.C1
Then, Will the query use the available index of T1. and Will i get better performance. Even a little improvement in performance may help me a lot as this kind of query is being used within a where loop (so it is going to be executed multiple times).
Please advise on this..
Regards,
Dipali..
Hi,
18:43:17 rel15_real_p>create table t1(c1 number primary key, c2 number);
Table created.
18:43:26 rel15_real_p>create table t2(c1 number, c2 number);
18:45:08 rel15_real_p>
18:45:09 rel15_real_p>begin
18:45:09   2  for i in 1..100
18:45:09   3  loop
18:45:09   4        insert into t1(c1,c2) values (i,i+100);
18:45:09   5  end loop;
18:45:09   6  commit;
18:45:09   7  end;
18:45:09   8  /
PL/SQL procedure successfully completed.
18:45:09 rel15_real_p>
18:45:09 rel15_real_p>
18:45:09 rel15_real_p>begin
18:45:09   2  for i in 1..100
18:45:09   3  loop
18:45:09   4        insert into t2(c1,c2) values (i,i+200);
18:45:09   5  end loop;
18:45:09   6  commit;
18:45:09   7  end;
18:45:09   8  /
18:45:23 rel15_real_p>select count(*) from t1;
  COUNT(*)
       100
18:45:30 rel15_real_p>select count(*) from t2;
  COUNT(*)
       100
18:45:49 rel15_real_p>select index_name,index_type from user_indexes where table
_name='T1';
INDEX_NAME                     INDEX_TYPE
SYS_C0013059                   NORMAL
18:48:21 rel15_real_p>set autotrace on
18:52:25 rel15_real_p>Select T1.C2
18:52:29   2  From T1, T2
18:52:29   3  where T2.C1 = T1.C1
18:52:29   4  /
        C2
       101
       102
       103
       104
       105
        C2
       200
100 rows selected.
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=100 Bytes=
          900)
   1    0   HASH JOIN (Cost=7 Card=100 Bytes=3900)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=100 By
          es=2600)
   3    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=100 By
          es=1300)
Statistics
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
18:52:31 rel15_real_p>analyze table t1 compute statistics;
Table analyzed.
18:55:35 rel15_real_p>analyze table t2 compute statistics;
18:55:38 rel15_real_p>set autotrace on
18:55:42 rel15_real_p>Select T1.C2
18:55:43   2  From T1, T2
18:55:45   3  where T2.C1 = T1.C1
18:55:46   4  /
        C2
       101
       102
       103
       104
       105
        C2
       200
100 rows selected.
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=100 Bytes=7
          00)
   1    0   MERGE JOIN (Cost=6 Card=100 Bytes=700)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Ca
          rd=100 Bytes=500)
   3    2       INDEX (FULL SCAN) OF 'SYS_C0013059' (INDEX (UNIQUE)) (
          Cost=1 Card=100)
   4    1     SORT (JOIN) (Cost=4 Card=100 Bytes=200)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=100 B
          ytes=200)
Statistics
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed
18:56:56 rel15_real_p>Select T1.C2
18:56:56   2  From T1, T2
18:56:56   3  where T1.C1 = T2.C1
18:56:58   4  /
        C2
       101
       102
       103
       104
       105
        C2
       200
100 rows selected.
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=100 Bytes=7
          00)
   1    0   MERGE JOIN (Cost=6 Card=100 Bytes=700)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Ca
          rd=100 Bytes=500)
   3    2       INDEX (FULL SCAN) OF 'SYS_C0013059' (INDEX (UNIQUE)) (
          Cost=1 Card=100)
   4    1     SORT (JOIN) (Cost=4 Card=100 Bytes=200)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=100 B
          ytes=200)
Statistics
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed- Pavan Kumar N
Related articles
  • Index usage in depending on where clause changes. 10-11

    Hello Friends, I need your help for one issue. I have one query , which is using two table Say T1 and T2, where C1 is common column using which both are joined. C1 is primary key in T1, but no index available in T2 for C1. T1C2 is the column which we

  • Function-based index with OR in the wher-clause 10-11

    We have some problems with functin-based indexes and the or-condition in a where-clause. --We use Oracle 8i (8.1.7) create table TPERSON(ID number(10),NAME varchar2(20),...); create index I_NORMAL_TPERSON_NAME on TPERSON(NAME); create index I_FUNCTIO

  • Response time of query utterly upside down because of small where clause change 11-30

    Hello, I'm wondering why a small change on a where clause in a query has a dramatic impact on its response time. Here is the query, with its plan and a few details: select * from ( SELECT xyz_id, time_oper, ...      FROM (SELECT                     

  • A bug? BC4J ViewObject where clause change doesn't affect view contents 11-30

    Hi! I've found the following interesting behaviour (maybe a bug?) of BC4J ViewObject caching: 1) make a viewobject with where clause and 1 parameter in it. 2) set parameter binding to some value 3) execute query 4) then change viewobject's where clau

  • Dynamic where clause change 11-30

    Hi, I have code as below Scenario1 FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Submit')) Loop End loop; Scenario2 FOR i in (Select .................... from table1 Where id in(select id from

  • Function-based Index and an OR-condition in the WHERE-clause 10-11

    We have some problems with functin-based indexes and the or-condition in a where-clause. (We use oracle 8i (8.1.7)) create table TPERSON(ID number(10),NAME varchar2(20),...); create index I_NORMAL_TPERSON_NAME on TPERSON(NAME); create index I_FUNCTIO

  • Dynamically changing the WHERE clause in detail VO in Master Detail 10-11

    Hi, I want to develop a search functionality, results will be in a table, which will have an inline table displaying the details per row found. I have a MD defined trough viewlink. I do change the master VO at runtime (set the entire SQL), based on t

  • Index Usage from SQL query in Oracle Forms 10-11

    Would using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX. We have these where clause in Oracle Forms Records Group. Below are two examples... 1. If we have a where clause with LIKE would that NOT use the in

  • How to create a procedure to output REF CURSOR with any WHERE clause? 10-11

    I have an requirement like this: I have huge query which need to reuse in my code more than 10 times. This SQL has about 50 lines. Thing is for those 10 odd times sometimes the WHERE clause changes (columns are the same). So I cannot create a view si

  • Dynamically adding filters in where clause in sql server 11-30

    Hello I have a question,  may be asked many times but could not find a thread, I have 8 parameters,... and user can select any of these parameters they can select 3,4 or all 8. I know we could use dynamic sql for this, because the sql is same.. just

  • Is it possible to create a view where table in the From clause changes name 10-11

    is it possible to create a view from a from a table like this create view my_view as select id, col1, col2, result from <<my_latest_cacahe_table>>; the table in the from clause changes the name . I have another table which indicates the the la

  • How to change access path for 'where' clause by using HINTS? 10-11

    I searched a loooot of posts and haven't found a solution for my case. I don't even know whether it is possible or not. Is it possible to change the sequence of Oracle "Predicate Information"? Here is my SQL and Oracle's execution plan.   SELECT

  • Using a dummy where-clause to force the use of an index 11-30

    I notice that an index only gets used when I use the index key in the where clause. Should I use a dummy where clause to force the index to be used? SQL> create table emp ( 2 empno NUMBER(5), 3 empname VARCHAR2(15) ); Table created. SQL> create inde

  • Index usage depends on columns selected 10-11

    Hi, somehow cannot understand why index is not used, please help. in Oracle Database 11g Release 11.2.0.3.0 - 64bit Production 1.     Included only indexed column and got a perfect plan explain plan for2 select s.x_cnt 3 from reported_summary s 4 whe

  • Performance - composite index with 'OR' in 'WHERE' clause 10-11

    I have a problem with the performance of the following query: select /*+ index_asc(omschact oma_index1) */ knr, projnr, actnr from omschact where ((knr = 100 and actnr > 30) or knr > 100) and rownum = 1; (rownum used only for test purpose) index: cr

  • How to change recordset bahaviour to accept dynamic column names in the where clause 10-11

    Hi im using php-mysql and i make a recordset and i want to make the column names in the where clause to be dynamic like "select id,name from mytable where $tablename-$myvar"; but when i do this my i break the recordset and it disappear and when

  • Two secondary indexes in a where clause 11-30

    Hi All, Can any one of you let me know wether DB optimizer will use two indexes if given in where clause........ example : primary index A,B.   secondary C,D,E. select ................ where A in ....                               B in ....          

  • How to change where clause in VO query in bean 11-30

    Hi experts I have to modify vo object query where clause on run time .. is it possible?? if yes please suggest me,as mentioned by Timo, it is very much possible see this following link - Changing the WHERE clause or VO Query at runtime in Oracle ADF

  • Change JDBC select SQL query's where clause at runtime 11-30

    Hi, I have one JDBC sender channel with Select SQL Query which helps in fetching data from JDE table. We have defined where clause to fetch some particular records i.e. if ABC = NULL then fetch the record.. and then update query is used to turn that

  • How to change operator of join conditions in where clause? 11-30

    Hello I have a situation... I want to change the operator between each join conditions in the where clause when these join conditions are not from the same join.. For example, I have the following schema: Dim1 ------ DimA -------Fact1 Dim1-------DimB