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
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?
During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...
In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature
FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)
RETURN NUMBER
In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER
Indextype was created as
CREATE INDEXTYPE test_index_type
FOR
test_eq(number, number)
USING index_methods
WITH ARRAY DML(number, sys.ODCINumberList)
[code]...
(problem occurs in all cases)
CREATE TABLE test_table (id NUMBER (19,0));
CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;
When attempting to insert data in the table insert into test_table values (1);
oracle raise exception
CODEError starting at line 53 in command:
insert into test_table values (1)
Error at Command Line:53 Column:1
Error report:
[code]...
So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?
By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number
Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?
FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)
I am attaching full sql script to recreate environment and reproduce the problem.Type definition:
CREATE OR REPLACE TYPE index_methods AS OBJECT
(
step number,
STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)
[code]...
What are the various collection types available in Oracle 9i.
Mention the differences between them.
How many different types of indexes we have in oracle?
View 2 Replies View RelatedI have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.
Here is my Cursor:
CURSOR c_chk_style IS
SELECT DISTINCT 1
FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
[code]...
Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.
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.
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 two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
Send all type of matrix report with examples.
View 1 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.
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.......
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.
set server output on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........
the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8
this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:
set serveroutput on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......
I get the result:
1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8
I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.
I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.
I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..
like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')
All nodes in RAC environment are available.But sessions are not load balanced for one/some of them.
Checking alert log the following oracle errors can be found:
alert_<INSTANCE>.log
ORA-21779: duration not active
ORA-06512: at line 1
The root cause of the problem is SMON that is not clearning up some transient types.
I've noticed this effect in Oracle 11g release 1, but there is a chance it happens for early Oracle versions.
Test data for the problem is as follows.
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 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> with t
2 as
3 (
4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all
5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all
6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all
7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all
[code].......
I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).
So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.
Datatype for the tables are :
Id : Number
compid : Number
Rel_Type : Number
enddt : Date
how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:
1 CREATE OR REPLACE TYPE object1 AS OBJECT (
2 object_id NUMBER(5),
3 object_name varchar2(10),
4 object_added DATE);
5 /
6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY);
7 /
8 INSERT INTO object1_tab
9 VALUES (1,'Daniel',sysdate);
10 /
11 show errors;
12 /
13 CREATE OR REPLACE TYPE object2 AS OBJECT (
14 object_id NUMBER(5),
15 object_job varchar2(10),
16 object1_ref REF object1 );
17 /
18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY);
19 /
20 INSERT INTO object2_tab
21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P
22 WHERE VALUE(p).object_id = &object_id));
23 /
24 select DEREF(object1_ref)
25 FROM object2_tab;
26 /
27 CREATE OR REPLACE TYPE object3 AS OBJECT (
28 object_id NUMBER(5),
29 object_location VARCHAR2(20),
30 object2_ref REF object2);
31 /
32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY);
33 /
34 INSERT INTO object3_tab
35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id));
37 /
38 show errors;
39 /
40 select object_id,object_location,DEREF(object2_ref)
41 FROM object3_tab;
42 /
As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:
OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF)
--------- -------------------- ----------------------------------
1 New York [SANTA.OBJECT2]
Contained within the object2 is the following:
SANTA.OBJECT2(1,'Developer','oracle.sql.REF@c4cb4aa6')
How would i view the ref for object1 within object3? I will also need to be able to verify the data in these REF in the new table they are used in.
need to use Extend() in nested tables in Oracle? What could be the problem if I do not use this method in my code?
I have a nested collection type (TABLE OF VARCHAR2(32)) declared in my package. My stored procedure takes the TABLE type as input and inserts that data into a database table.
I see that my code works fine without using EXTEND method.
I'm trying to determine why subtype creation is not working in 11G R2 XE, while it works in 11G R2 EE.
Environment is XE 11.2.0.2 on Windows 7 X64 (yes, not supported but I don't have anywhere else to try this).
Here is a test case, using the SYSTEM account as that is what the developers use (yes, terrible practice) :
--type
-- this works, creates the type
CREATE OR REPLACE TYPE [schema].prod_type AS OBJECT (
pid INT,
pprice NUMBER,
MEMBER PROCEDURE display(pid IN NUMBER))
NOT INSTANTIABLE NOT FINAL;
[code]....
I've tried adding the schema name after UNDER, e.g. UNDER [schema].prod_type with the same result.
How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
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`
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 RelatedI'm trying to call a custom made PL/SQL function in a SQL query. I want to supply the values of the parameters during the query. I can call the function if I "hard code" the parameter values, but when I try to supply them I get the ORA-06553 error.
This call works:
select pkg_tm_import_util.wb_screen_hr_refresh_func('','','','','','','','','','','','') from dual
However, this does not, but should be the same as the call that works:
select pkg_tm_import_util.wb_screen_hr_refresh_func(
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||
[code]....
i am getting PLS-00306: wrong number or types of arguments in call to 'SECURITY_AUDIT_DTL_TYPE' error below code.
CREATE OR REPLACE PROCEDURE load_data_audit_trail_dtl
AS
TYPE security_type IS TABLE OF SECUIRTY%ROWTYPE
INDEX BY PLS_INTEGER;
security_type_var security_type;
[code]....
I am a programmer in Oracle PL / SQL in Oracle 10g I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic). Is this possible and how? NLS_LANG ?
View 2 Replies View RelatedI am using 11.2 DB. I have created 2 object types. The contact_t has been embedded into the student_t object and I have created a view with the nested object.How do you select the values from the contact_t object type in SQL from the view? i want to see the values of the contact_t object in speerate fields (ie. contact_name, city, state...)
CREATE OR REPLACE TYPE contact_t AS OBJECT ( dcid NUMBER(10), contact_name VARCHAR2(50), city VARCHAR2(50), state VARCHAR2(10), zip VARCHAR2(10), email VARCHAR2(100) )
CREATE OR REPLACE
[Code]....
