What Kind Of Index Would Improve Performance Of Queries

Jun 30, 2013

It seems certain queries search by the number of days to ship(number of days between the order and shipping dates). What kind of index would improve the performance of these queries?

View 2 Replies


Performance Tuning :: Materialized View To Improve Search Queries?

Jan 31, 2012

In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables

Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.

And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.

since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query

Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view

Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;

Will it improve performance of the search functionality

View 2 Replies View Related

Performance Tuning :: How To Improve The Performance Of Export Job (expdp)

Dec 6, 2011

I have an issue with export(expdp).

When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.

expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk

Do i need any look into any memory parameters for this?

View 1 Replies View Related

SQL & PL/SQL :: Why Do Stored Procedures And Functions Improve Performance

Feb 10, 2012

Why do stored procedures and functions improve performance?

A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.

I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?

View 1 Replies View Related

Partitioning Table To Improve Load And Query Performance?

Nov 20, 2012

I have a big table in which we load about 37M recrods. We have informatica ETL which Loads the data in bulk Mode and creats index after completion. The data load takes about 1Hr and Index Creation takes about 1/2 hr. In total it takes about 90 to 95 Mnts.

Now I thought if Partition and Load paralley, It will improve perfromance. We did 4 partition and and each Partition about 9M records. The data load in Bulk mode is completing in 25 Mnts. Again When I am creating index over it, It is taking about 40 Mnts. and in Total Load time is 65 Mnts.

Is there way I can better performance to complete the load in 1/2 hr ?

View 2 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

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

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

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

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

Performance Tuning :: How Length Of Column Width Effects Index Performance

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

Performance Tuning :: Sub Queries With Function Call?

May 24, 2011

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same:



View 6 Replies View Related

Performance Tuning :: Queries Of Materialized Views

Jan 28, 2011

We are using the below query to create a Materialized View but it has been running since 3 hours. It is an Oracle 9i database running in HP-UX.The quey is as follows,

/*+ use_nl(A) parallel (A,4)*/

View 21 Replies View Related

Using Rownum In PL/SQL Can Significantly Reduce Performance And Throughput Of Queries

Nov 27, 2010

Using rownum in PL/SQL can significantly reduce performance and throughput of queries.

For example,

CODEselect *
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...)
where rownum < 500

takes much more time on a heavy loaded db than

CODEselect Y.*
from (
select X.*, rank() over(order by ...) rnk
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...) X) Y
where rnk < 500

I suspect it's because Oracle optimizer goals all_rows and first_rows.

View 2 Replies View Related

Performance Tuning :: How To Check SQL Queries Run By Application At Backend

Oct 28, 2010

Name some database tool from which I can check the SQL Queries which my application is running.

NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.

View 4 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session

View 3 Replies View Related

Performance Tuning :: Does Parallel Hint Works In Cursor Queries

Aug 28, 2013

Does parallel hint works in cursor queries? The cursor query is something like :

cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;

The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.

View 13 Replies View Related

Performance Tuning :: Queries On PGA Settings And Related Memory Management?

Feb 11, 2011

I have few queries on PGA memory management.Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same

As I understand many workareas can be allocated to a single sql statement and number and sizes of theses workareas is controlled internally by Oracle when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set) Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used

[1] Can we say paging happens and can be checked at this time?

[2] Is there a difference in handling memory while populating pl/sql tables?

As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.

With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all while there wasn't any heavy OS process involved during the period. I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.

[3] Isn't it expected to match?

[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?

select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;

During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)

SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4


The above query is showing above values even when the pl/sql block execution is completed 30 minutes back

[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?Of course I am not checking at OS level as mentioned in question [3] above the values won't match!

Still the output of free command for reference(After the pl/sql block executed)

SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444

--(After the pl/sql block executed)

SQL> select * from v$pgastat;
aggregate PGA target parameter 524288000bytes
aggregate PGA auto target 456256512bytes
global memory bound 26214400bytes
total PGA inuse 17328128bytes


[6] What could be the significance of negative values of 'session pga memory/max'?

Last We have an OLTP system and in the night we run batch processes in 2-4 sessions

Suppose I have 10 GB RAM and with PGA setting of 3.5 GB Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning

[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?

Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size

[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?

View 8 Replies View Related

Performance Tuning :: Hierarchical Queries - Run Jobs Within Stipulated Time Frame

Nov 17, 2011

We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.

The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. I have attached in the text file.

View 4 Replies View Related

SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade

Jul 4, 2013

I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
select col1 into var1 from table where colno = myparam;

Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.

View 7 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

SQL & PL/SQL :: Kind Of Transpose Table

Sep 1, 2011

My need is to build columns for each month in table and find changes between monthes. My first try works fine, but next step should be performed using wider period.

with t2 as (
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 840 as currencyid, 3649509 as dealid, 7.5 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 12 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 12 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 13.5 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 13 as avalue from dual union all

As you can see, I've got virtual table named jan2008, feb2008, mar2008, apr2008 left-joined to tt1 table. But what should I do with much more wider period, for example 5 years? Can I do it without creating virtual tables for each month?

View 7 Replies View Related

SQL & PL/SQL :: What Kind Of Privileges Can't Be Assigned To Roles

May 2, 2010

I read that few privilegs can not be granted to roles like 'UNLIMITED TABLESPACE ' and they need to granted to user directly.

1. Is there any reason behind that few privileges cant be granted to roles or is it just oracle design.
2. what kind of privileges cant be assigned to roles.

View 7 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

Server Utilities :: What Kind Of Backup By Looking At Export Dump File

Jan 2, 2012

Is there a way to know what kind of Backup (table/tablespace/full/schema) by looking at export dump file ? If yes, can you tell me the command ?

View 1 Replies View Related

PL/SQL :: What Kind Of Exception Can Raise A Select Statement Excluding NO_DATA_FOUND

Oct 23, 2013

what kind of exception can raise a select statement excluding NO_DATA_FOUND; For example i try to run the following: select * from departments where departments_id=11; In a situation like that what kind of error oracle can raise?I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.

View 25 Replies View Related

Composite Index - Performance?

Nov 8, 2012

I have a lot of queries on a table with WHERE clause:


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

Performance Tuning :: SQL Not Using Index From Application?

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.

DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah19,099,1891,846,735,884
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah0 1,846,735,884


View 9 Replies View Related

Performance Tuning :: STOPKEY And Index Use

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));

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

Performance Tuning :: How To Create Index

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 ::--


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

Performance Tuning :: Index Creation?

Aug 9, 2010

is there any way to reduce the index creation time.

in my case one index creation took 5 minute and there are 5 indexes , so it took 25 minutes.

View 7 Replies View Related

Performance Tuning :: Index With IN Statement

Jan 18, 2012

I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).

But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!My scale indices and used Selects:

CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
WHERE datum BETWEEN '20120117' AND '20120117'
AND flag_inst = '1' )
AND kontro = '9876521'

View 12 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved