SQL & PL/SQL :: How To Know Index Is Created On Table Or Not
Jun 30, 2011how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
ADVERTISEMENT
SQL & PL/SQL :: Date Search - No Index Created On Table
Feb 25, 2010DT1 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')
All Index To Be Created In Different Tablespace Like Medium_index
Dec 27, 2011I have created queue table using below script
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE => 'scott.NG_MSG_QUEUE_TBL',
QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE',
COMPATIBLE => '10.0.0',
[code].......
Indexes
--------
INDEX_NAME TABLESPACE_NAME
------------------------------ --------------------------
AQ$_NG_MSG_QUEUE_TBL_T MEDIUM_DATA
AQ$_NG_MSG_QUEUE_TBL_I MEDIUM_DATA
SYS_IL0001100359C00037$$ MEDIUM_DATA
SYS_IL0001100359C00040$$ MEDIUM_DATA
SYS_IL0001100359C00041$$ MEDIUM_DATA
SYS_C0073180 MEDIUM_DATA
I want all the index to be created in different tablespace like 'medium_index. can we specify tablespace for index when we are creating queue table.
Can Index Be Created On Virtual Column?
May 17, 2011 what my issue is with this virtual column
CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS
[code]...
Lastly, can an index be created on a virtual column? I am thinking no since it calculated?
Performance Tuning :: Index Has Been Created On Both Depart_id For Two Tables
Jul 30, 2010SELECT department_id
FROM (SELECT department_id
FROM employees
UNION
SELECT department_id
FROM employees_old )
WHERE department_id=100;
[code]....
The index has been created on both depart_id for the two tables. The only difference between the two I observed was the 1 recursive call for the 1st sql.and also, one additional view in the plan.There is a little difference in bytes sent over the network.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
[code]....
Is there any performance impact you find in those above two sqls if you compare?
PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let'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
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I 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 RelatedPerformance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies View RelatedServer Administration :: Find When Database Role Created And Who Has Created?
Feb 14, 2013Is there a way to find when was a database role created and who has created?
View 5 Replies View RelatedREFRESH GROUP Is Automatically Created When A Materialized View Is Created?
Aug 17, 2012tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies View RelatedPL/SQL :: How Many Foreign Key Can Be Created On Table
May 3, 2013in a table how many foreign keys can we create.
View 10 Replies View RelatedSQL & PL/SQL :: Created Table With Date In Database
Feb 22, 2011Me working on Oracle application Express,there are lots of tables created in it, how will i get to know who created which table and on which date.
View 7 Replies View RelatedSQL & PL/SQL :: Make Partition Of Table Which Is Already Created
Sep 21, 2011I want to make partition of table which is already created and has huge amount of Data I have done indexing already
my structure are as follows
EMPLOYEE CHAR(10),
MONTH_YEAR NUMBER (6),
TYPE CHAR (1),
SALARY_CODE CHAR (5),
AMOUNT NUMBER (16,2)
Indexing on Employee,Month,Salary_Code
How can I do partitioning on this table.
SQL & PL/SQL :: How To Find Who Created The Table In Oracle
Feb 8, 2011somebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies View RelatedForms :: Use Created Table In Data Block?
May 1, 2013I have data that the user is modifying on the form and a data block that is supposed to reflect those change (summaries) So I created
TYPE VehicleInfo IS RECORD (Vehicle_Desc ctl_fleet_vehicles.Vehicle_Desc%Type,
Miles timecapture.miles%Type,
Hours TimeCapture.Equipment_hours%Type);
TYPE VehicleTab IS TABLE OF VehicleInfo
INDEX BY BINARY_INTEGER;
VehicleObj VehicleTab;
I fill this up with the correct data, but I would like to be able to use this as a table in the data block (called Vehicle_Summary) In the past I have piped tables into data blocks from the server, but I'm unsure how to use a table I created in forms in the Query Data Source Name of the data block.
PL/SQL :: Created A Table With CLOB As Datatype For One Of Columns
Aug 5, 2013<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies View RelatedPL/SQL :: Created Partition And Parallel Degree 4 On Table A
Oct 10, 2012I have table A with size 120 Million and two more tables are of size 2 Million on Table B and less than 1 Million size on table C.I had created Partition and Parallel degree 4 on the table A. Created table B with Parallel degree 2 and Created table C with NOPARALLEL.
My query is using above tables with joins and inserting into table D using HINT /*+ APPEND NOLOGGING*/I had executed the explain on the above criteria the cost is showing 20767.
Later created Tables A,B and C with NOPARALLEL. Applied HINT on Table D /*+ APPEND NOLOGGING*/ and als applied HINT /*+ PARALLEL(A, 4) PARALLEL(B C, 2) on select query which uses to insert into Table D.
My question which is best practice on PARALLEL degree creation at table level or query level:
a) Creating table with Paralle (degree 4)
b) Applying HINT /*+ PARALLEL (TABLE A , 4) */ at query level
SQL & PL/SQL :: Employee Table In Scott Schema Created By Default
Oct 3, 2011I am using Oracle 11g Release 11.2.0.1.0
OS: Windows
I am using the Employee table in Scott schema which created by default.
10,20,30,40,50 are the department ids.
I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids
Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30
Forms :: Created Block For Table On Tabular Style - Validating Item?
Oct 10, 2011I created a block for EMP table on tabular style. if in first line empno entered is 1then i don't want to allow the end user to enter 1 in second line. but when-validate-item is not working because first_record and next_record is not allowed. I need to validate before saving the data into emp table and immediately after empno is entered into empno item.
View 16 Replies View RelatedServer Administration :: Event Alert Created Successfully On Custom Table But Not Firing?
Mar 8, 2013I have created custom table in inventory and after register table & columns, i create event alert on it. event alert is not firing.
View 1 Replies View RelatedServer Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?
Feb 7, 2012We 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 ?
SQL & PL/SQL :: Full Table Scan On Index Table
Feb 3, 2012when 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 ..
PL/SQL :: Load Data From Index By Table To Table
Jun 27, 2013We 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 .
SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master
Mar 6, 2010I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
SQL & PL/SQL :: Use Of Table Index?
Feb 10, 2012Am 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;
How To Partition And Index The Table
Oct 24, 2011how 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?
SQL & PL/SQL :: NULL Index Table Key Value
Sep 26, 2010We 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?
SQL & PL/SQL :: Two Index Exist On Same Table
Apr 19, 2011Can 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));
PL/SQL :: Index On A Staging Table
Jul 10, 2012I 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.
Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow
Jun 27, 2012I 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.