I have, for example, two tables: COMPANIES and EMPLOYEES. COMPANIES has a primary key on column COMP_ID. EMPLOYEES has a foreign key EMP_COMP_ID. I always do query COMPANIES with WHERE clause on COMP_ID and COMP_ADDRESS. What indexes on COMPANIES should I create in this case? First unique on COMP_ID, second composite on COMP_ID and COMP_ADDRESS? Or one unique index on COMP_ID and COMP_ADDRESS will be enough?
View 5 RepliesI have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
what is the best practice to implement in Indexing,is it global indexing or local indexing, I would like implement one of them in object that has been partitioned horizontally.i dont know exactly what to make of it.
View 9 Replies View RelatedI'm Using Oracle DB, I have got performance issue in one of my portlet, Can Re-Indexing work for Increasing the performance.
View 3 Replies View Related Does Oracle supports an index that only contains a sub-set of the records in a table?
For eg - A table has 1 million records, can we have a index which contains suppose 100k records. We have used a similar logic in DMSII in Unisys. Not so sure if Oracle supports it.
i want to ask about indexing in partition table. i have table that indexed by local index. when i want to select all data. I execute this query
select * from Book_Issue_Part where status='Pinjam';
but it does not select all data, only partly data have selected. is it a wrong query to select all data in indexing partition table? so what query should i execute to get all data.
however im taking database size into account. i believe there has to be some sort of medium when it comes to infrastructure and performance.
this is specific to tables which map other tables. these tables that im creating usually consist of only 4 columns. 3 columns are mapped ids and the other a date in field. the 3 columns need to be indexed because they are used in joins within views. also those fields in the other tables are indexed as well.
DB: Oracle 10g R2
OS: SUSE Linux SP 1 x864
We are maintaining a DR of our Database Server(oracle 10g R2 atop SUSE SP1 Linux) using Platespin(
[URL]......
Platespin is set to replicate(block based), incremental data(delta) every 1.5 hour from Production to DR site over a 30 Mbps dedicated fiber link.
Our maximum changes of data per day(during business hours) wont exceed 300 MB. During business hours Platespin replicates at least 1 GB at every replication cycle, while during off hours it replicates 300 to 500 MB per replication cycle. We are facing this strange issue with this box only(SLES 10 SP1 + Oracle 10g R2), we have protected MS Exchange 2007 Server based workloads without this strange issue, i.e in case of Exchange only delta replicates from Production server to DR site on Platespin.
Platespin support says us that Oracle re-indexes its database for better performance, so it is possible that re-indexing causes the blocks level changes on the storage, and since Platespin works on Block level, thats why it replicates so much(even though data is not changed that much)
here is actual words of Platespin support
<snip>
I think whenever Oracle database Indexing happens, it changes almost most of the Blocks of database and Platespin replicate all those Blocks.
As you know, Platespin checks the Date/Time attribute of every blocks before replication and if Date/Time attribute changes from last replication, it considers as changed block and replicate those blocks on Platespin Appliance. So, my suggestion is just look into the Oracle server behaviour before/after Data indexing process and do needful or do some workaround to overcome this issue.
</snip>
is there anything we can do at oracle level ?
I have two databases called
a) Old Retailer database
b) New Retailer Database
We cant delete old database but we are using and we want to use both of them.
Now problem
Both dbs have a table called retailer.
I want to prevent duplication in them.
I.e it there is a retailer 12 created in old one new one shldnt allowo us to create retailer
id---12
similarly if there is a retailer 13 in new one it shouldnt be created in old table
retailer ids are unique. its not the case that with retailer id for example 5 there is a
different retailer in these two tables of diffferent DBS. Its unique
Now I have to put this criteria
how can i do it?
Second questions===================
if a primary key is auto increment. how can i skip some numbers lets say it is 1,2,3,4
i want that when next record shld be inserted it shld be 6 not 5. i dont want to feed the value 6. i want to know how to change indexing of primary key to skip some numbers when its set to auto increments.
I want to insert data from table A and dump into table B.The data in Table A looks like this:
Level_IDCode Type
ETY_1A......... 100000116
ETY_1B......... 100000116
ETY_1C......... 100000116
ETY_2AA........ 100000117
ETY_2AB........ 100000117
ETY_2AC........ 100000117
ETY_2BA........ 100000117
ETY_2BB........ 100000117
ETY_2BC........ 100000117
ETY_3AAA....... 100000118
ETY_3AAG....... 100000118
ETY_3AAI....... 100000118
ETY_3AAX....... 100000118
[code]....
And I want to dump the data into Table B which will look like this:
Level_ID Code Type Parent_cd
ETY_1A.........100000116null
ETY_1B.........100000116null
ETY_1C.........100000116null
ETY_2AA........100000117A
ETY_2AB........100000117A
ETY_2AC........100000117A
ETY_2BA........100000117B
ETY_2BB........100000117B
ETY_2BC........100000117B
ETY_3AAA.......100000118AA
ETY_3AAG.......100000118AA
ETY_3AAI.......100000118AA
[code]....
I need to populate a column called Parent Cd with the values that sits at the base level of the hierarchy. So, if the code A sits at the base level then its parent is NULL and the parent for code AA, AB, and AC will be A and parent for AAA, AAG will be AA and so on.The level_IDs are upto 10 and there are close to 400,000 records.
Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????
Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?
I've the following data and i need the parent and leaf most child,including their id's
Create table par_chld (id,p_id,c_id) as
(select 1,900,501 from dual union all
select 2,900,502 from dual union all
select 3,900,503 from dual union all
select 4,100,900 from dual union all
select 5,200,900 from dual union all
select 6,300,400 from dual union all
select 7,101,500 from dual union all
select 8,102,500 from dual union all
select 9,103,500 from dual union all
select 10,201,600 from dual union all
select 11,201,601 from dual union all
select 12,201,602 from dual )
In the above data
p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503
And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child
Expected output
id_p p_id id_c c_id
4 100 1 501
4 100 2 502
4 100 3 503
5 200 1 501
5 200 2 502
5 200 3 503
6 300 6 400
7 101 7 500
8 102 8 500
9 103 9 500
10 201 10 600
11 201 11 601
12 201 12 602
I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p
select connect_by_root p_id p_id ,id id_c,c_id
from par_chld
where connect_by_isleaf =1
start with p_id not in (select c_id
from par_chld
where c_id is not null)
connect by nocycle p_id =prior c_id
getting the id_p column as expected above.
way to achieve the below logic.
SQL to pick up parent child relationship within same table with a certain logic.
Example:
mod_product_numberProduct_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888
[code]...
Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.
If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.
I have one requirment like below
Table1 have records like below
parent childflag
null.....A......Y
A........B......Y
B........C
C........D
D........E......Y
I want it should be like
parent child flag
null......A.....Y
A.........B.....Y
B.........E.....Y
I want to replace 'D' (parent of 'E') with any of the upper level which has Flag 'Y' (first level parent which satisfy falg 'Y')
Is there any SQL query which can return first value. I have 1 table, 2 columns: NR; created_from_nr;
Data:
Nr=L1 created_from_nr=''
Nr=L2 created_from_nr=L1
Nr=L3 created_from_nr=L2
Nr=L4 created_from_nr=L3
How can I get Nr=L1 if I know L4, with one query ?
Would there be any problem / error when we import the dump file (which has parent table) on target database ?
View 11 Replies View RelatedI have a table called FAMILY which holda the data like , for father_gen_1 --->father_gen_2 --> father_gen_3 -- son.
father_gen_1 father_gen_2 father_gen_3 son
ABC XYZ GEF MNC
ABC lmn kth
I want to get the output which is a three column heirarical table and holds the parent child relationship Like following:-
id name parent_id
1 ABC NULL
2 XYZ 1
3 GEF 2
4 MNC 3
5 LMN 1
6 KTH 5
i have a table with data as follows:
select genres.* from genres
data is as follows
INTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
14430015Biography 157 0100
14430015Classics & Poetry 173 0116
14430015Literature & Anthologies 175 173118
now when i give
select level,genres.* from genres connect by prior INTGENREPAGEID= INTPARENTID
i get
LEVELINTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
114430015Biography 157 0100
114430015Classics & Poetry 173 0116
214430015Literature & Anthologies 175 173118
114430015Literature & Anthologies 175 173118
i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.
I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.
SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*
ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found
how to delete Parent table records without affecting to child table dependent records?..
View 5 Replies View Related--Create a parent object
CREATE OR REPLACE TYPE PARENTOBJ AS OBJECT
(
FIRST_NAME VARCHAR2(50),
static function GETNAME return varchar2,
MEMBER PROCEDURE getoutput
)
NOT FINAL;
/
----Create the body for parent object type
CREATE OR REPLACE TYPE BODY PARENTOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'PARENTOBJ';
END ;
MEMBER PROCEDURE getoutput iS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||PARENTOBJ.getname );
END ;
END;
/
--Create a child object
CREATE OR REPLACE TYPE CHILDOBJ UNDER PARENTOBJ
(
static function GETNAME return VARCHAR2,
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput
)
FINAL;
/
----Create the body for child object type
CREATE OR REPLACE TYPE BODY CHILDOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'childobj';
END ;
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput IS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||CHILDOBJ.getname );
END;
END;
/
The above types are created with no issues. However, when I am trying to inherit the child method and override its parent, I am getting the follwoing error:-
--NOW INHERIT THE PARENT INTO CHILD, AND OVERRIDE THE PARENT METHOD BY CALLING THE CHILD METHOD
DECLARE
OBJ PARENTOBJ;
BEGIN
OBJ := NEW CHILDOBJ(NULL);
DBMS_OUTPUT.PUT_LINE('obj method getoutput() = ' || GETOUTPUT());
END;
/
ORA-06550: line 6, column 56:
PLS-00201: identifier 'GETOUTPUT' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
i want to update primary key of 1 master and 2 child table is it possible if i do it simultaniously if not
View 9 Replies View RelatedI have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...
My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??
create table PARENT
(
event_id NUMBER,
event_title VARCHAR2(50),
net_id VARCHAR2(16)
);
--truncate table parent;
insert into parent
(event_id, event_title, net_id)
values
(1, 'title1', 'CURLY');
[code]...
i need to delete a record from table but it showed a error for foreign key constraint so i disabled the constraint and again deleted, now the row is deleted.
Again inserted another values instead the deleted value. after that i tried to alter the constraint but i received the error ORA-02298 cannot validate - Parent keys not found
alter table t1 enable constraint FK_T1;
But i am not able to enable the key. what to do?
I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
MASS_MASTER --parent table.
MASS_CHILD --child table.
The below query is used to delete records from parent table.
DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);
The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.
I need to get the super set(parent) record from a relational table. Following is the structure of the table,
create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),
primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);
[code].....
Here '-10' will be considered as parent(super set) for all other values. If i have records like below,
-10 200 300
100 200 300
123 200 300
521 201 300
Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).
I have two forms, FormPARN, FormCHLD.
One Text item is there in FormPARN, near to that Text item, Button is there.While clicking Button FormCHLD will be opened. Inside the FormCHLD, i will do some calculation and i need to store the result in FormPARN's Text Item.
How can i do it?
I have a table like below:
EMP_ID MGR_ID EMP_NAME EMP_ROLE EMP_HIERARCHY
10 9 John Developer 7,8,9,10
9 8 Charlie Manager 7,8,9
8 7 Bruce GL 7,8
7 King CEO 7
20 18 Jack Developer 7,16,17,18,20
18 17 Adam Teamlead 7,16,17,18
17 16 Erik Manager 7,16,17
16 7 David GL 7,16
We can see that the for each employee, there is a corresponding Manager tagged except for CEO of the company. Now, I want the output to be like below
EMP_ID EMP_NAME EMP_ROLE MGR_ID MGR_NAME MGR_ROLE SUPERVISOR_ID SUPERVISOR_NAME SUPERVISOR_ROLE
Here, MGR_ID is nothing but the MGR_ID from the same row and Supervisor_id is nothing but the MGR_ID of MGR_ID
Ex:- For 10 EMP_ID, the MGR_ID = 9 and Supervisor_id = Mgrid of 9 i.e., 8
For 8, the Mgr_id = 7 and Supervisor_id = Mgrid of 7 which is none. etc.,
The same logic applies for Mgr_name, Mgr_Role , Supervisor_name and Supervisor_role too. I could not format the data in a tabular format due to some formatting issues.
There is table:
Column A | Column B |
X | X |
X | Y |
Y | X |
Y | Y |
I have selected view with "group by rollup":
ID | Column A | Column B |
1 | | |
2 | X | |
3 | Y | |
4 | X | X |
5 | X | Y |
6 | Y | X |
7 | Y | Y |
Now I want to display it in client app like a tree.So, I need create ID's of parents in child rows, like this:
ID | Parent ID | Column A | Column B |
1 | | | |
2 | 1 | X | |
3 | 1 | Y | |
4 | 2 | X | X |
5 | 2 | X | Y |
6 | 3 | Y | X |
7 | 3 | Y | Y |
I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?
View 2 Replies View Related