I have index on column column1 and because of the nvl, the optimizer is not picking up the index
How can I restructure the query to avoid nvl giving same result using index on column1?
now column1 and column2 are varchar2 fields here
where nvl(column1,'*')=(nvl(:param1,nvl(column2,'*'))
can I use case statement like
case when column>'' then .. stuckup here
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
somehow cannot understand why index is not used
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 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2674489506
-------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 306 (8)|
|* 1 | INDEX FAST FULL SCAN| S_NUI01 | 306 (8)|
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
2. Included some other column and got TABLE ACCESS FULL
explain plan for2 select s.x_cnt,s.ru_id
3 from reported_summary s
4 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335
---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
3. Included all other columns and got TABLE ACCESS FULL as well explain plan for2 select s.x_cnt,s.* 3 from reported_summary s 4 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335
---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
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?
Env: RHEL 5.8 RAC 11.2.0.2
I'm currently moving an IOT from one database to another using expdp/imdp. The IOT is non-partitioned and about 100GB in size containing ~1,1 billion rows.
The dumpfile contains nothing else but the IOT. I'm importing with no special parameters, no pre-created IOT, just ordinary dumpfile import. (impdp username/password dumpfile=impdp:iot.dmp nologfile=y )
During import I got unable to extend TEMP errors from impdp.
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
I had to add 2 additional files to my Temp tablespace (total 96GB of temp) before the import could finish off.
Is this temp usage to be expected when importing IOT's ?
The below query is not performing good.
SELECT * FROM wedb_master
WHERE c_group IS NOT NULL;
The table is having 200000 records.
I have index on c_group but not used. FTS is happening.
how improve the performance of IS NOT NULL predicate.
For a hash join statement, is it beneficial to have the join condition objects in the index as well as the objects in the where clause?
View 19 Replies View RelatedIn which condition it is required to rebuild the index and how can identified it?
View 12 Replies View RelatedI have an oracle code that is using Oracle External table to load a file.The issue: the column within the files have NA within them which needs to be skipped. Mentioned the code below -
create table lric_arc_fac1_xt
(
cost_category varchar2(30),
component varchar2(30),
fac number(38,18),
lric number(38,18),
marginal number(38,18),
sacs number(38,18),
dlric number(38,18),
dsac number(38,18),
[code].....
Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies View RelatedI am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
We have Oracle 8i, 9i, 10g and 11g. I installed oracle client 11gR2 and when I tried connecting to the database, its erroring out saying "ORA-03134 connections to this server version are no longer supported". What client version should I install in order to avoid this error?
View 2 Replies View RelatedI have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,
[code]...
I have a query that performs the sum of a field and divides by the number of occurrences, got me a problem because if there are no occurrences would cause a divide by zero error in Query.
The solution I found might not have been the best, but at the time was possible.
I put a Case, when the sum of the number of occurrence is zero then return zero, otherwise performs the division of the sum of the values by the number of occurrences.
SELECT CLIENT_ID,
TO_NUMBER(TO_CHAR(DTPROC, ''YYYYMM'')),
DTPROC, EXCHANG_VAL,
[Code]....
Now this query is very slow, because have CASE,SUM and GROUP BY, so I need to optimize it.
do this validation (if the divisor is zero) more performance?
select T2.name,sum(T1.area)
from (select * from country) T1,T2i wrote very simple just to show you the problem, bu i think here we should add "group by".
but is there any way to avoid group by?because i don't want to group any thing!
oracle11g
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 RelatedI have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
I have created a procedure which sends mail to the users by using the smtp operation. I am collecting the email id of the users and then concatenating the same using the loop.
Everything is working fine, but if some person is coming multiple times in the loop, then his/her name is shown multiple times in the CC/BCC part (Although mail is sent once), it should be restricted to one time.
what i am doing is:
I have declared id_collector and it is concetenating the email as per the condition loop
........ some code here....
id_collector:=id_collector||email_id||';';
end loop;
Now, I want to check before concatenating that email already exists or not.
Example:
suppose value of id_collector= 'abc@gmail.com;pqr@gmail.com;rst@gmail.com;xyz@gmail.com;'
and again if email id comes - 'abc@gmail.com', then it should get rejected.
I think it can be done using reg_exp but I have messed up with that.
i am facing problem with date column.when i am trying to do execute a query this works fine
select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')
and when i am trying the below query its showing the only this year dates its not referring the previous years
select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')
when i am trying below query its showng the error ORA-01843: not a valid month
select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
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?
How to force an index if the table not using the index?
View 10 Replies View RelatedWe have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
I'm creating oracle form to allow the user to register a new record in a table, I want to check the duplication in Four tables! which means, the user is able to save the record if it's NOT already registered in the other four tables.By checking P_ID (user parameter). this is my
Declare
v_count number;
begin
select count(*) into v_count ---Checking the 1st table
from 1_table
where p1_id=:p_id;
if (v_count>0) then
message ('Duplicate');
[code]....
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,
While I am inserting and updating the values in the oracle database its showing a message 'FRM-40401 - No changes to save.' I don't want this message to be shown. How can I do that?
View 5 Replies View RelatedBest solution to avoid mutating table error?
View 11 Replies View Relatedi have created form. one field is there "Payment_terms" at line level. for that i created LOV containing 3 fixed Payment terms. my requirement is that Avoid the user to select twice the same value from LOV.
View 8 Replies View RelatedIn my database is present undesirable parallelization of the query. All involved tables has degree=1,
if I right understood there is no method to avoid this except reducing of degree of indexes ? I'm using 11.2.0.3
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
select dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State, cl.ClientNo, cl.Name as "Client Name", inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date, to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt
[code]...