|
Hi i want to split one large table into two. But here my problem is, i need to query from both the tables, which should give the data same as the parent table.
For example If the large table is like
COLA COLB
1 A
2 B
3 C
4 D
5 E
and if i split this into
FIRST table
COLA COLB
1 A
3 C
5 E
Second table as
COLA COLB
2 B
4 D
Now i want to query the two tables, so that i should give the value order by COLA. Result shold be like
COLA COLB
1 A
2 B
3 C
4 D
5 E
My small attempt...
|
|
|
|
|
This should do the trick:
select cola, colb
from(
select cola, colb from table1
union all
select cola, colb from table2
) d
order by cola
But I don't see why you should split a table in two.
Wout Louwers
|
|
|
|
|
Actually i have one billion records in this table all are logs from a server machine. But only some specific error logs are used to process. To get better performance i am planning to do the same
My small attempt...
|
|
|
|
|
You'd be better off archiving out old records and properly indexing your main table.
|
|
|
|
|
i am creating some reports by parsing the error logs in that tables. SO i need all the error logs in the table. That y planed to move irrelevant data.
The table is indexed...
Any other technique?
My small attempt...
|
|
|
|
|
Look into partitioning the table by periods.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That also did.. Right now i have different partition files to keep data for this table
My small attempt...
|
|
|
|
|
hello
Is there a SQL Profiler for Oracle? I want to do:
1. intercept any database/storedproc calls, examine parameters ..etc
2. identify performance bottleneck for example
Thanks
dev
|
|
|
|
|
|
Check out the tools available from Quest for Oracle. The have great products.
http://www.quest.com/spotlight-on-sql-server-enterprise/[^]
If you need a consultant, I've got a great guy for you. He is a former Oracle employee (18 years), specializing in Oracle performance tuning. He has worked majic for me in the past. Email me privately and I will give you his contact info.
|
|
|
|
|
I'm overworked, too exhausted and might actually be willing to hire extra help - this is what I am looking for (basically Oracle tuning and troubleshooting) ...
1. Use TOAD to intercept database calls, also stored proc calls, examine parameters etc.
2. Use TOAD to do performance monitoring
your email if interested in freelance job? I won't do this now, but will probably to set up external IP (buy new domain, hardware... etc and have my business which will happen next ... 2-3 months I hope, by then I can recruit your professional assistance and you can teach me the tricks remotely)
dev
|
|
|
|
|
Keep posting your questions here and I'm sure the folks will help you out. 
Try this query for finding SQL statements that have more than 10,000 reads:
select disk_reads,sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc
Oracle has a tremendous amount of views that you can use to find performance bottlenecks.
There are tons of books out there on the subject of Oracle tuning.
Good luck with your endeavors.
David
|
|
|
|
|
|
I want to add more record to tblStaff. before add it i want to check if existing this iD...Please help me.
|
|
|
|
|
declare
@Exists int
Select @Exists = count(*) from tblStaff where StaffID = @StaffID
If @Exists = 0
Begin
add record
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This query has a better performance:
if not exists(select * from tblStafff where StaffID = @StaffID)
Begin
-- Add record
End
Wout Louwers
|
|
|
|
|
i got half way through the reply and started adding additional bits, should have started again  .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
first , i'm very thank for your help ... but can you tell me more detail ? can you give me some sample...i'm a new learner.
|
|
|
|
|
All 3 of us supplied sample code - use Wout's it is the best.
As a learner you should have a book to work through the examples! You need some basic knowledge to begin with...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thank 4 your advice...now i'm checking my book other sample in my class
|
|
|
|
|
Here is the Stored Procedure
ALTER PROCEDURE SP_CHECKDUPLICATEID
-- Add the parameters for the stored procedure here
(@ID INT)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- VARIABLE DECLARATIONS
IF ( (SELECT COUNT(*) FROM TBLSTAFF WHERE ID=@ID) = 0 )
BEGIN
INSERT INTO TBLSTAFF(ID) VALUES(@ID)
END
END
GO
hope it helps
Niladri Biswas
|
|
|
|
|
I have got two queries both of which generate the same execution plan:
query 1:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID) itemNet
query 2:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) itemNet
ItemNetwork table has 4 columns:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemType] [tinyint] NOT NULL,
[NetworkID] [int] NOT NULL
I have also created a non-clustered index on ItemNetwork table:
CREATE NONCLUSTERED INDEX [IX_ItemNetwork_ItemID_ItemType__NetworkID] ON ItemNetwork
(
[ItemID] ASC,
[ItemType] ASC
)
INCLUDE ( [NetworkID])
The first query takes one second to execute, while it takes 2 minutes for the second one to execute. The execution plan for both queries is the same. You can see the execution plan for the first query here[^] and for the second query here[^].
The only difference that can be seen between the two execution plans is the amount of data that comes out of news table. For the second query, we see a very big arrow coming out of news table. That is because the actual number of rows coming out of this table is 1534672 rows while for the first query, this number is 877 rows. For both queries, the estimated number of rows is 10 (because of top 10 clause). Look at the actual number of rows for both queries here[^] and here[^].
The only difference between the two queries is this condition:
ItemType = 0
I also updated the statistics for all the tables involved, but it didn't make any difference.
Could somebody please tell me how I can make the second query execute as fast as the first one?
p.s. the total number of rows in News table is 1576612 rows, in Network table 1820 rows and in ItemNetwork table 42164 rows
modified on Thursday, June 25, 2009 3:19 AM
|
|
|
|
|
Is it possible that the index could be fragmented? Not that it should make such a significant difference but might be worth checking.
|
|
|
|
|
I looked at it. The fragmentation is 0%
|
|
|
|
|
How to code connection string for retriving database when is online?
How to code dynamic connection string?
Phumlani Sandile Kunene
Country South Afica
Province KwaZulu Natal
City Vryheid
Township Emvuyane, eS'mashwini
Room number 540
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
