SQL & PL/SQL :: Create Index On Multiple Tables?
May 21, 2013we create index on multiple tables in oracle
View 12 Replies
ADVERTISEMENT
How To Create SP For Taking Count Of Multiple Tables
Dec 7, 2012i have 20 tables in schema and wanted to take count using store procedure.
how to create a sp for taking count of multiple tables
SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle
Apr 8, 2012I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......
SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.
PL/SQL :: Create Multiple External Tables From Same Flat File?
Nov 28, 2012using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?
Jul 11, 2012Can we create non-cluster index on a clustered index?
View 5 Replies View RelatedServer Utilities :: Loading Multiple Input Files Into Multiple Tables
Jul 9, 2012NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
Index In Partitioned Tables
Jan 2, 2013I would like to ask about indexes in partitioned tables.I have indexes on a partitioned table, it is partitioned by range method i.e based on Creation date time.All select queries sent to the table use the Creation date time. I have an index on Creation date time.Here is an example:
SELECT col1, col2, col3
FROM table1 where
date_time BETWEEN TO_DATE ('20120117 10:00:00','YYYYMMDD HH24:MI:SS')
AND TO_DATE ('20120117 13:00:00','YYYYMMDD HH24:MI:SS')
AND frmt_name = 'XXXX'
AND sender = 'YYYYY'
AND nature = 'ZZZZ'
AND type LIKE '548'
ORDER BY date_time
WHERE ROWNUM <= 5000
[code]....
do I have to add DATE_TIME to all indexes (IX_NAME_FORMAT_TYPE,IX_CCY) or not?
Flashback Tables - Recover Index Name?
Dec 27, 2011I fired following commands .
>create table t1(id number);
>create index indx_t1 on t1(id);
>drop table t1;
>flashback table t1 to before drop;
but when i fired
>select index_name , table_name from dba_indexes where table_name='T1';
it shows index name something like "BIN$QvTWVG3dTxmBBJ607f9CWQ==$0" so is their any way to recover that index name
SQL & PL/SQL :: Separate Index And Tables In Two Different Tablespace
Apr 14, 2013How to saperate inndexes and tables in two different tablespcae.......???
View 2 Replies View RelatedSQL & PL/SQL :: Index On Global Temp Tables
Jun 16, 2010I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..
create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;
create index lmn on abc_tab(a,b,c)
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 :: Create Bitmap Index?
Jan 27, 2013i want to create a bitmap index,but getting the error shown below..i created primary key on column dname of dept and unique constraint on empno of emp.
SQL> create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno;
create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-25954: missing primary key or unique constraint on dimension
Performance Tuning :: Creating Index On Base Tables Of A View?
Dec 9, 2010I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?
The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.
SQL & PL/SQL :: How To Sum Over Multiple Tables
Feb 21, 2010I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
SQL & PL/SQL :: Multiple Tables
Sep 30, 2010Video Rental Shop
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
How To Create Index On Materialized View
Aug 6, 2010How to create index on materialized view which is refreshed daily basis.
View 3 Replies View RelatedCreate Reverse Key Index On Table
Feb 25, 2013difference 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.
SQL & PL/SQL :: Create A Table With Constraint And Index
Apr 7, 2010I 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
Performance Tuning :: How To Create Index
Mar 30, 2013I 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 ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
SQL & PL/SQL :: Create Primary Key Without Creating Index?
Jul 12, 2012how to create a primary key with out creating an index?
View 10 Replies View RelatedSQL & PL/SQL :: Create Table As Select With Index
Nov 10, 2010Is 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;
SQL & PL/SQL :: To Create A Bitmap Index On Partition Key
Dec 3, 2012I have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.
I gave ALTER TABLE .. MOVE MAPPING TABLE;
But getting the below error,
ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.
How To Create Unique Index On A View
Aug 24, 201211.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?
SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here
Selecting Via Multiple Tables
Jan 19, 2011I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
SQL & PL/SQL :: Get Sum Amounts From Multiple Tables?
Mar 2, 2011I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.
For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.
I am pasting the test data below:
create table employee (eno number(9), ename varchar2(40));
insert into employee values(1,'Jack');
insert into employee values(2,'Jim');
[Code]....
I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.
Is there any better way to write this query in terms of performance?
SQL & PL/SQL :: JOIN For Multiple Tables
Mar 29, 2011Here is what I'm trying to do:
I have three tables:
TABLE1 TABLE2 TABLE3
NCI 1AB OPEN
NCI (NULL) (NULL)
INV (NULL) (NULL)
NCI 2AB REOPEN
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU",
l.loc AS "LOC",
l.qty AS "QTY",
o.ncikey AS "NCI",
r.description AS "NCI DESC",
o.qtyexpected AS "NCI QTY EXP",
o.qtyreceived AS "NCI QTY REC",
o.loc AS "NCI LOC",
o.status
[Code]...
SQL & PL/SQL :: Getting Data From Multiple Tables
Feb 11, 2011What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies View RelatedSQL & PL/SQL :: Merging Into Multiple Tables
Feb 12, 2012Can I use MERGE statement to insert the same thing into multiple tables?
Like below--
Merge into tbl1
using tbl2
when matched then update...
when not matched then insert all.
SQL & PL/SQL :: Selecting Via Multiple Tables?
Jan 19, 2011I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
SQL & PL/SQL :: Trigger From Multiple Tables
May 1, 2013I need to set up a trigger to check and enforce that the age of an employee at hire date is older than 18 when a new record is inserted. The age is on the PERSON table, and the hire date is on the EMPLOYEE table.
My attempt:
CREATE TRIGGER AGE_HIRE
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF FLOOR EXTRACT (YEAR FROM (PERSON.BIRTHDATE - EMPLOYEE.HIREDATE)) > 18
THEN DROP NEW
ELSE INSERT NEW
FROM PERSON, EMPLOYEE
END;