SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies


ADVERTISEMENT

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

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

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

SQL & PL/SQL :: Full Table Scan On Index Table

Feb 3, 2012

when i am Executing the following statement

SELECT DISTINCT EXPOSURE_REF FROM KBNAS.VW_EXPOSUREDETS_FOR_CCYREVAL
WHERE EXPOSURE_CURRENCY='THB' AND BASE_TXN_CCY='USD' AND BRANCH_CODE='7000'
AND (REVAL_STATUS='O') AND CONV_RATE<>'62' AND (EXPOSURE_AMOUNT<>0)
UNION
SELECT DISTINCT ED.EXPOSURE_REF FROM KBNAS.EXPOSURE_DETAILS ED,
[code].....

I have attached DDL for table EXPOSURE_DETAIL(PARTITION),LEDGERCARD,LEDGERCARDDETAILS, DDL for INDEX on those tables and DDL for Views..

Issue: we have created the Indexes but when we check the explain plain .. full table scan is going on..I have attached the explain plan ..

View 11 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

How To Partition And Index The Table

Oct 24, 2011

how to partition and index my table for a special problem.

The table:

CREATE TABLE TEST (
ID NUMBER PRIMARY KEY,
U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000
O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000
CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >>
);

As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.

The application has only 8 query which will always use a WHERE clause like this:

SELECT * FROM TEST
WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3

1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?

2. how to partition this table?

View 2 Replies View Related

SQL & PL/SQL :: How To Know Index Is Created On Table Or Not

Jun 30, 2011

how to know weather procedure is working or not in a database ..

how to know index is created on table or not.

View 2 Replies View Related

SQL & PL/SQL :: NULL Index Table Key Value

Sep 26, 2010

We are getting the below error on a piece of code that chas been functioning fine for over 4 years..

ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "pd.pf33", line 148

The line it's pointing at is merely a FOR loop that opens a cursor (select query) and loops through the items.

FOR rec IN csr_cash
LOOP
END LOOP

This piece of code has been functioning fine for years.. and never got this kind of error on a for loop. Could it be something internal to Oracle/underlying memory issues?

View 8 Replies View Related

SQL & PL/SQL :: Two Index Exist On Same Table

Apr 19, 2011

Can there be an impact on performance if below two index exists on the same table:

t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));

View 5 Replies View Related

PL/SQL :: Index On A Staging Table

Jul 10, 2012

I have 2 similar tables. Same columns. Data inserted to second table from first for a condition and first table is truncated. Then data moved from second to first and second table truncated. Second table is to store data for a small period of time. First table has indexes. Do I need any index on second table?

The query on second table is always insert into second select * from first. Similarly insert into first select * from second.

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

What SQL Command To Find Index Of A Table

May 14, 2007

1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.

P/s: I don't have OEM installed so I must use SQL command

Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

View 2 Replies View Related

Create Reverse Key Index On Table

Feb 25, 2013

difference between the ways we create Reverse Key Index on the table.The two ways are as below:

1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ;
2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;

Which one is the appropraite one.
Are both going to act in the same way.

I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.

SELECT *
FROM <Table_Name>
WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;

The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.

View 3 Replies View Related

SQL & PL/SQL :: Bulk Insert In Index Table

Jul 8, 2013

why bulk insert is not possible in a table which has index?

View 9 Replies View Related

SQL & PL/SQL :: Create A Table With Constraint And Index

Apr 7, 2010

I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?

create table test_const(ename varchar2(50) not null,
key_num number not null,
descr varchar2(100),
constraint constraint_test_const unique (ename, key_num));
create unique index test_const_idx on test_const
(
"ENAME","KEY_NUM"
)
tablespace tmp_data;

Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram

View 3 Replies View Related

SQL & PL/SQL :: Create Table As Select With Index

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

SQL & PL/SQL :: Use Of Index For Searching Data In Table

Mar 5, 2010

i want to know the use of index for searching data in table...tell answer to my query with table example....

View 2 Replies View Related

SQL & PL/SQL :: Index Of Table Type Object

Apr 4, 2012

I have a table type object loaded (tto) with data and am using it as :

SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;

This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?

View 16 Replies View Related

How To Disable Index In Partition Table

Aug 11, 2010

I am trying to disable the index i am getting the error like

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

the table have partition. how to disable the index in partition table.

View 1 Replies View Related

SQL & PL/SQL :: Drop Index Which Has Constraint On Table?

May 18, 2011

How to drop an index which has a constraint on the table?

while droping the index on the table wheather i should remove the

constraint or disable the constraint or without droping the

constraint

View 1 Replies View Related

SQL & PL/SQL :: Transaction Table - Index Recreation?

Aug 3, 2010

There is a transaction table which becomes very slow after 3-4 months in spite on regular weekly analyze and index recreation.

But once we drop the constraint (PK) and recreate it again it becomes ok. The same doesn't happen even when we rebuild the index.

View 2 Replies View Related

Comparing Index And PK Of A Table Between Instances?

Dec 2, 2012

I am about to compare Indexes and PK's(Constraint) of 3 instances.

What DBA table/s should i use best? The comparison is done to a specific schema only.

PS:Which is better dba_ind_columns or dba_objects?

View 10 Replies View Related

Index Creation In Partitioned Table

Jul 20, 2012

Facts:
Oracle 10.1.0.5
AIX 5.3

My table, HMTX have 10 partitions each of one have 6 millions of rows (average). We have 7 partitioned LOCAL indexes in that table.
Every month we load data into a new partition (6 million of rows aprox) and drop the oldest partition in table HMTX.

In order to do that we have a script that contain the next statements:

drop of all indexes
drop index n1;
drop index n...;
drop index n7;

[Code]...

create indexes again with tha same storage and degree parameters
CREATE INDEX hmtx_TST_N1 ON hmtx (campo1, campo2, campo3 .... campo8)
TABLESPACE xxxx
PCTFREE 0
INITRANS 2

[Code]....

My problem is in rhe index creation section, despite use parallel with degree 8 and nologging the index was created in :

Elapsed: 02:43:50.85.

In past months that index was created in :
Elapsed: 01:43:36.94
Elapsed: 04:48:31.24
Elapsed: 00:57:16.28

there are another way in order speed the index creation ?? o another way to disable ths index ??

View 4 Replies View Related

How Does Local Index Work In Partitioned Table

Nov 28, 2012

I have table with 4 partition by range partition. I am loading the table in bulk mode to latest partition. Before I load , I dropped the index and after Load I will be creating index. So when I am dropping index, it is dropping index from all the partitions and when creating the index, I am creating the index for all partitions. When I am creating index using local, it is telling you have to create local index for all partitions at the same time. because of that I have to drop and recreate all indexes again. Again I have to gather stats for whole table .

I was thinking we can build index for one partition and index should remain as is for old partitions If this is not the case, how do I plan my load for a partitioned table using bulk mode to latest partition.

View 4 Replies View Related

SQL & PL/SQL :: Date Search - No Index Created On Table

Feb 25, 2010

DT1 is a column of date datatype and there is no index created on the table

I want to add the below lines in the sql

TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'

Which one should I use in where condition to query and why?

1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')

View 6 Replies View Related

Server Utilities :: How To Take Exp / Imp For Index Organized Table

Dec 2, 2011

I am trying to take exp / imp for Index organized table.

View 16 Replies View Related

SQL & PL/SQL :: Index Effect While Inserting Records In A Table

Dec 17, 2011

I have one indexed column in a table.for performance improval purpose i created index to that column .

my doubt is..it may decrease the performance while inserting data into that column?

View 5 Replies View Related

SQL & PL/SQL :: Unable To Create Index On Large Table

Sep 30, 2012

I am trying to create a new index on large table of size around 100GB. but i am getting the following error:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.

temp tablespace size is : 20 GB.

does it mean that the whole index will be created at temp tablspace first?

View 3 Replies View Related







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