Multi Column Index Select Performance
Dec 11, 2012
explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.
A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.
View 2 Replies
ADVERTISEMENT
Oct 13, 2011
I have a table with, for example, three columns: A, B,C.
I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2
Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?
View 4 Replies
View Related
Jan 24, 2012
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
View 4 Replies
View Related
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
View 24 Replies
View Related
Sep 8, 2009
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete
[code]...
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
[code]...
View 10 Replies
View Related
Apr 2, 2011
here is my query
SELECT CURRENTSTEP
FROM (SELECT ( WFENTRY.NAME
|| ','
|| CURRENTSTEP.STEP_ID
) AS CURRENTSTEP,
(CASE
WHEN WFENTRY.NAME IN
[Code]...
in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?
here is how I use it
select
sys_audit_id
from
( SELECT
*
FROM
(SELECT
F.FINDING_NUMBER,
[Code]....
I saw adding this as a subquery with the filter and CURRENTSTEP = ? is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?
View 3 Replies
View Related
Oct 10, 2013
We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data
SELECT *
FROM address
WHERE address1 = 'a'
[Code]....
View 9 Replies
View Related
May 18, 2012
I am working with following select clause:
select distinct S.ID ID
from
ods.hso_Scheduled H,
ods.SO_SCHEDULED S
where
S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
and H.ID=S.ID
Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.
I have created following indexes on these tables:
Indexes on SO_SCHEDULED:
Index name Column name
SS_IDX1ID, SO_SUB_ITEM__ID
SS_IDX2INSERTION_DATE
SS_IDX3ID, INSERTION_DATE
SS_IDX4ID, SO_SUB_ITEM__ID, INSERTION_DATE
SO_SCHEDULED_ID_PKID
Indexes on HSO_SCHEDULED:
HSS_IDX1ID, SO_SUB_ITEM__ID, LAST_UPDATING_DATE
HSS_IDX2ID, LAST_UPDATING_DATE
HSS_IDX3ID
My problem is despite of having relevant indexes present, my query is not hitting them and hence the performance is very bad.
Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 574170360
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 814K| 38M| | 9574 (1)| 00:02:15 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 814K| 38M| | 9574 (1)| 00:02:15 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 814K| 38M| 185M| 9574 (1)| 00:02:15 | Q1,02 | PCWP | |
|* 4 | HASH JOIN | | 2653K| 124M| | 9564 (1)| 00:02:14 | Q1,02 | PCWP | |
| 5 | PX JOIN FILTER CREATE| :BF0000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| SO_SCHEDULED | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | P->P | HASH |
| 12 | PX JOIN FILTER USE | :BF0000 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| HSO_SCHEDULED | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("H"."ID"="S"."ID")
9 - filter("S"."INSERTION_DATE">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S"."INSERTION_DATE"<TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
View 6 Replies
View Related
Jan 28, 2011
I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'
So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting
here are the steps
Initially I kept the column Nullable
SQL> create sequence s5;
Sequence created.
SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.
SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';
SQL> create index i5 on t5(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit
SQL> alter session set events '10046 trace name context forever, level 12';
select *
from
t5 where id is not null order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************
select /*+ index(t i5) */ *
from
t5 t where id is not null order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04
Now I modified the 'id' column to Not Null
SQL> alter table t5 modify id not null;
SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';
COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y
14 rows selected.
select *
from
t5 order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************
select /*+ index(t i5) */ *
from
t5 t order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45
select id
from
t5 order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89
Questions are
1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
View 5 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Aug 31, 2010
My understanding of DB_FILE_MULTIBLOCK_READ_COUNT parameter is that it affects only Full Table Scans and Fast Full Index Scans - all other disk retrieval is single block.If so, then maybe I'm reading this trace incorrectly:
select /*+ first_rows */ pk
from test_join_tgt
where pk >= 0
and rownum > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.48 27.77 22368 22134 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.49 27.78 22368 22134 0 0
[code]...
What the heck is going on with the Multi-block reads in the Range Scan?
View 3 Replies
View Related
Nov 19, 2010
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
View 2 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Feb 3, 2011
I am doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have written the code for program now i found that select statement is return multi rows.
code is
when_button_pressed trigger.
declare
fdcode dmas.dcode%type;
fcodno dmas.codno%type;
famtrs dmas.amtrs%type;
fdate donres.rdate%type;
begin
[code]......
View 4 Replies
View Related
Mar 8, 2013
I have a 'Select List' widget (P_FILTER) which I have set to return multiple values. In my report region, I have something like this
Select A from B where B.Col_1 IN upper(:P_FILTER)
When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?
View 2 Replies
View Related
Jul 30, 2012
Earlier i used select list item type with single select values so, According to my requirement in where clause i have written the condition like this
NVL(publish_id, :P91_PUBLISH_ID) = NVL(:P91_PUBLISH_ID,publish_id)
but my requirement is multi selection. How could i write same condition for multi selection.
write the same condtion for multi selection.
View 8 Replies
View Related
Sep 16, 2010
I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
View 7 Replies
View Related
Jan 10, 2011
What is the difference between index rebuild and index rebuild online.
View 3 Replies
View Related
Oct 24, 2011
I want to design a report in different numeral(English,Arabic). I am using application server as a middle tier.
Like
empno name salary english salary arabic
34232 John 5000 in arabic digits
45454 Clark 6500 in arabic digits
I have already change environment as a parameter Eng to Arb or vice versa. but i need both language digits at a time.
View 3 Replies
View Related
Jun 28, 2012
I want to read a record that has 6 columns
RECORD |Col1 |Col2 |Col3 |Col4 |Average |Standard Deviation
0001____|Null_|5___|8___|10__|8.75___|2.986079
With SQL I want to calculate Average and Standard Deviation.
View 4 Replies
View Related
Jul 23, 2013
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
View 7 Replies
View Related
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Mar 22, 2013
I am describing a SQL statement to get it's column list:DECLARE
cur NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;
[Code]....
Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?
View 4 Replies
View Related
Nov 8, 2012
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
View 1 Replies
View Related
Jul 26, 2011
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
View 2 Replies
View Related
Nov 10, 2010
Is it a possible to create table using clause below together with index ?
create table the_table
as
select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:
ORA-01031: insufficient privileges when
...executing
Therefore I think about to create table together with index.
begin
B.proc.cre_table;
end;
View 6 Replies
View Related
Jun 6, 2013
We have a DELETE statement when coming from application is not using index but when run from Toad or SQLplus as same user uses index. Explain plan also shows using index.I did a query on v$sql below is the output of the query( I have attached the same as a txt file). All the stats are up to date and confirmed from the developer the variable B1 is using the same datatype as column MAXMKY.
SQL_TEXTSQL_ID DISK_READSOPTIMIZER_HASH_VALUE
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah19,099,1891,846,735,884
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah0 1,846,735,884
OPTIMIZER_COST HASH_VALUEPLAN_HASH_VALUE MODULEPARSING_SCHEMA_NAME
[code].....
View 9 Replies
View Related
Nov 6, 2010
I am working on a query for a feedback response system which is going to be targeted at the common case when the user only want the most recent 10-20 rows in the feedback table. My though is to create an index on the date column, do a sort in an inner query and rownum <= in an outer query. This works as I expect when I am only querying the main table (lookup by index with a stop key), but when I start joining the main table to attribute tables I end up with a full table scan of the main table with the stop key applied after all the joins are completed, the index is nowhere to be found.
CREATE TABLE attr1_tbl(attr1_id NUMBER NOT NULL, attr1 VARCHAR2(10) NOT NULL,
CONSTRAINT attr1_pk PRIMARY KEY (attr1_id));
CREATE TABLE attr2_tbl(attr2_id NUMBER NOT NULL, attr2 VARCHAR2(10) NOT NULL,
CONSTRAINT attr2_pk PRIMARY KEY (attr2_id));
CREATE TABLE attr3_tbl(attr3_id NUMBER NOT NULL, attr3 VARCHAR2(10) NOT NULL,
CONSTRAINT attr3_pk PRIMARY KEY (attr3_id));
[code]....
One thing I noticed was that when no data is selected from the attribute tables, even if they are joined in the query, the CBO throws them out of the plan and only accesses the main table. With the foreign keys this makes sense and really just disqualified my first thought that maybe I was missing a foreign key or not null constraint somewhere.
I also added the cardinality hint to overcome the chance that in my test case there was so little data that index access is not worth it.
View 15 Replies
View Related
Mar 30, 2013
I am going through this scenario:
* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 |
| 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))
This unselective index scan on step 36 of the explain is returning 14 rows but optimizer is selecting 3064 K rows from the table .
I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilized .
how to index this whole expression ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
View 3 Replies
View Related
