Index On Oracle 11g - Get Results Faster?

Nov 9, 2010

i have table (MEN) with 900,000 records.in this table i have field `IP` and `Tdate`.when i run query:

select * from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

it takes long time until i get Result.i try to make index like this:
create index
my_in
on
MEN (IP,Tdate );

but how to run the query to get fast Result?i try this:

select My_in from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

and get error: `ORA-00904`

View 1 Replies


ADVERTISEMENT

Select Statement Gives Different Results Using Bitmap Or Normal Index

Jan 10, 2012

we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.

The following SQL statement:

SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......

gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.

View 4 Replies View Related

Performance Tuning :: Query Sql Server Faster Than Oracle?

Feb 21, 2013

I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.

View 1 Replies View Related

Restarting Oracle Services Through Services.msc Is Faster Than Manual

May 17, 2013

Restarting oracle services through services.msc is faster then manual (In command prompt).

Even If we have heavy transaction on the oracle database But we can restart the database within minutes by using services.msc But It will take more time in command prompt(sql>shu immediate). So shall we assume here that services.msc is using shut abort command while restarting the database?

View 3 Replies View Related

Why A Certain Date Makes Query Faster

May 25, 2012

I have a query with a few tables in join, and I filter data with something like

where mytable.initial_date > sysdate-30

If I use any date, it takes about 5 seconds to run, but if I use

to_date('01010001','ddmmrrrr')

instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?

View 7 Replies View Related

PL/SQL :: Truncating Or Drop Table Is Faster?

Jul 8, 2012

I have a table with 80 million records. Is dropping this large table is faster or truncating is faster?

View 6 Replies View Related

RMAN :: Restore / Recovery From Snapshot Faster?

Aug 8, 2012

Version : 11.2 on Solaris, AIX

All our production DBs are backed up by some Hitachi tool (apparently it is called ShadowImage) . Apparently they put all the tablespaces in Backup mode and 'take a snap' . Our BAU team says this is faster.

On an event of DB Crash Is it easier to restore, recover from these 'snapshots' ? Are these snapshots much faster than RMAN ?

View 3 Replies View Related

PL/SQL :: Records Return Faster Inline Rather Than With Or Depends On Situation

Jan 15, 2013

I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.

Here is the query using WITH:

with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N'  and student_status = 'AS' then 0 else 1 end as enreg_stat,

[Code]....

View 7 Replies View Related

Server Utilities :: Faster Impdp Excluding Indexes And Constraints

May 24, 2012

We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints

Steps
Import schema excluding constraints,rf_constraints,indexes with metadata only
Import schema with data only
spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes
execute on Destination the Index creation ddl
execute on Destination constraints and rf_constraints ddl

View 1 Replies View Related

PL/SQL :: When Oracle Invalidates Result_cache Results Without Any Changes In Objects

Jul 30, 2012

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
ret number;
begin
select t.val into ret from rc_table t where t.id=p_id;
return ret;
exception
when no_data_found then
return null;
end;

/And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:

1. "select for update" from this table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".

I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases:

[URL]......

But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating. In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?

View 2 Replies View Related

SQL & PL/SQL :: Connecting From Oracle To Excel To Copy Results In Cell Value

Aug 29, 2012

How can I connect excel from pl / sql to copy results from the query. I was trying with OLE2 but it is throwing error as "invlid Identifier". Then how to include OLE2 in oracle.

View 10 Replies View Related

Connect To Oracle DB And Write SQL Results Into Excel File

Sep 27, 2013

I would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).

my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl

select count(*) from emp;
select count(*) from dept;

print the same SQLs in A column and result should be in B column in excel file.

column A column B
select count(*) from emp; 14
select count(*) from dept; 4

thats it.

View 1 Replies View Related

Server Administration :: Difference In SQL Results From Oracle 9i To 11gR2?

Jan 2, 2011

I've migrated from Oracle 9i to Oracle 11gR2, when i'm checking my application on Oracle 11gR2, i found that some of the query result are differed from the previous version of Oracle 9i, which is very illogical to me. I've checked the data for all related tables which are involved in one of the query. I've checked all indexes also. Still i'm not convinced that Oracle is doing anything wrong. I cannot recreate the whole scenario here, because of many tables involved in that query.

One of the sample query code.

select 'ProductMaster' producttype,e.schemecd,e.SCHEMESHORTDESC as SCHEMEDESC,to_char(e.schemefrdate,'dd/MM/yyyy')as schemefrdate, to_char(e.schemetodate,'dd/MM/yyyy') as schemetodate,e.mkid ,e.countrycd,e.statecd,e.districtcd,f.schemetype, a.productcd,a.packsizecd,a.packtypecd,a.sortorder1, a.countrycd,a.statecd,a.districtcd,a.p_uniqueid,a.mrp, (a.productcd ||'-'||b.countryname||'-'||c.statename||'-'||d.districtname|| ' -pkt.prc:'||a.pktprice ||' -mrp:'||a.mrp) as pcode
from wb.wbproductdetails a

[code]......

View 17 Replies View Related

Oracle 11g - Record Query And Time To Fetch Results For A Specific User ID

Jul 22, 2013

I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?

View 7 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I 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.

View 5 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

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I 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.

View 5 Replies View Related

SQL & PL/SQL :: How Many Types Of Index Are There In Oracle

Nov 11, 2011

I read about many indexes as B tree index, function based index,Bitmap Index etc.how many types of Index are there in Oracle and what are they? wat are the categories and sub category? when to use what?

View 6 Replies View Related

Oracle Sys Index Rebuild?

May 28, 2012

Below Table Index are rebuild,This table are relation with sys user or not?after rebuild index, getting error in alert log file

ORA-00600: internal error code, arguments: [psdnop-1], [1410], [], [], [], [], [], [], [], [], [], []
ORA-01410: invalid ROWID

when trying to select * from dba_network_acls getting error massage Rowid invalid

ALTER INDEX CTXSYS.SYS_C003684 REBUILD;
ALTER INDEX CTXSYS.SYS_C003683 REBUILD;
ALTER INDEX CTXSYS.SYS_C003682 REBUILD;
ALTER INDEX CTXSYS.SYS_C003681 REBUILD;

[code]...

It is possible to recreate this index again.......

View 1 Replies View Related

SQL & PL/SQL :: Types Of Oracle Index

Sep 26, 2011

Types of oracle index in detail. consider this situation, if a select query without any where clause takes much time to get data,then we will create index for that table.

Now again run the same select query. this time, whether the query takes much time again or will it work fast? whether the index is used for a select query execution or not?

How we can confirm that whether the oracle using the index normally?

View 2 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

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.

View 3 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

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We 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.

View 5 Replies View Related

9.2.0.8.0 Oracle Index Not Used In Order By Desc?

Oct 30, 2011

There are 2 setup of Oracle DB 9.2.0.8.0. There is a table T1 in both (one that is working 6.5 million rows and the other that is not working having 6.7 million rows). while doing - select * from t1 order by c1 desc; one is using index i1 and other is going for full table access.

View 1 Replies View Related

Use Oracle Hint To Use Index In Query Which Uses UNION?

Mar 31, 2011

I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.

Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.

The select statements inside the inline view returns huge number of row (like 50 million rows).

The whole query fails with time out.

How can I optimize this query further?

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 4 Replies View Related

SQL & PL/SQL :: Local Index Creation In Oracle Partitioning?

Jul 6, 2010

I have a table which if approc 5GB in size.I am trying to create a partition table from it. I created the new table,but when i am creating local indexes, i am getting an error when i create a unique key. I ll post the creation scripts:

CREATE TABLE A_PT_ORDREG
(
BUS_UNIT VARCHAR2(5) NOT NULL,
ORDER_NO VARCHAR2(15) NOT NULL,
PT_CODE VARCHAR2(15) NOT NULL,
REG_NO VARCHAR2(15) NOT NULL,
AS_APPLICATION VARCHAR2(5) NOT NULL,

[code].....

THE INDEX:

CREATE UNIQUE INDEX PK_A_PT_ORDREG ON A_PT_ORDREG (BUS_UNIT, ORDER_NO, SEQ_NO) local;

I am getting the following error:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

View 4 Replies View Related

Performance Tuning :: Why Oracle Doesn't Use The Index

Dec 2, 2010

I have a table "NEWS_COMMENT" like this:

Name Type
------- --------------
ID NUMBER(8)
USERID NUMBER(8)
SORT_TEXT VARCHAR2(100)
TEXT VARCHAR2(1000)
DATE DATE
VALID VARCHAR2(1)
CODNEW NUMBER(10)

The table has a normal index for the userid column.

There is a query that looks for the differents CODNEW for a USERID but allways the CODNEW has to be greater than 2248833

select codnew from news-comment where userid=2914655 and valid='N' and codnew>2248833

I have created a new index for this kind of querys

create index coment_new_IDX on news_comment
(CASE WHEN codnew >2248833 and valid='N' THEN userid ELSE NULL END )

but oracle doesn't use it. I have used a hint to force it but doesn't run.

View 9 Replies View Related

Performance Tuning :: Why Oracle Do Not Use Predefined Index

Apr 7, 2011

Here, let me explain:

I have create a table with 8 million records and 2 different indexes using 2 different columns (columns name NUM1 & NUM2) on that table. First indexed column (NUM1) values have many different values (1,2,3... etc).

Second indexed column (NUM2) values have only 2 different values.
7999999 records values is same("A") and remaining one record values is different("B").

Query1:
select * from tbl where num1=val

Query2:
select * from tbl where num2='B'

I have compare explain plan both queries, but Query2 doesn't use predefined index. Why Oracle don't use my redefined index at column NUM2?

View 5 Replies View Related

Check Index Used In Oracle Select Query?

Mar 21, 2013

I have partitioned an oracle table and created Local Index for the Partitioned table .

Now i want to make sure that the Local index is being used when i perform select query on the Table partition.

How do i confirm that ? can i check the explain plan generted for the select query to confirm local index is being used ?

View 1 Replies View Related







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