Database

18-Dec-08 9:03

ptr_Electron wrote:
what is the command on the MYSQL promt to invoke editor to write Stored procedure in MYSQL

If you mean MySql COmmand Line client, I'm not aware of such command. However you can write the procedure for example in the notepad and the paste it to the prompt or use source option to run a file as a script.

The need to optimize rises from a bad design.My articles[^]

Execution Plan

Mycroft Holmes17-Dec-08 16:47

Mycroft Holmes

17-Dec-08 16:47

Just want to get some feedback on an execution plan
if 86% of the cost of a query is a clustered table scan I assume there is little or no options for optimising the query. On a 10 minute query this is the highest single cost.

Never underestimate the power of human stupidity
RAH

Re: Execution Plan

Wendelius17-Dec-08 18:15

17-Dec-08 18:15

I think it depends on the surrounding operations and how many rows are you expecting from that operation. For example if just a few rows are fetched using clustered index scan and they do not reside near each other, it would be more efficient to fetch them using the tree vertically.

However, it's hard to say if the plan can be better without seeing the whole plan and the sql statement (well actually also info about the indices and row amounts). Do you have any possibility to post the plan and the sql?

The need to optimize rises from a bad design.My articles[^]

Re: Execution Plan

Mycroft Holmes17-Dec-08 18:23

Mycroft Holmes

17-Dec-08 18:23

Thanks Mika - I was hoping for a quick confirmation but I found some ways to improve the performance.

This is actually 1 of 9 queries in the procedure and it takes 46% of the total, 2 queries take over 90% of the cost and each has a clustered index scan cost over 80%

The query retuens 3.4m rows from a base table of 16m with lots of linkages. I went through and turned all the joins into inner joins and did lots of preparation and reduced the query from 34 min to 10 min so I'll have to settle for that for now.

Never underestimate the power of human stupidity
RAH

Re: Execution Plan

Wendelius18-Dec-08 8:47

Built-In Functions Myth!!!

18-Dec-08 8:47

Mycroft Holmes wrote:
I was hoping for a quick confirmation

I guess I've been teaching and consulting for so many years that's it's quite impossible to get a quick or exact answer from me Smile | :)

Mycroft Holmes wrote:
each has a clustered index scan cost over 80%

I've noticed that the db engine is very eager to use clustered indexes and to perform horizontal tree scans on clustered indexes. There has been situations where simply replacing a clustered index with a nonclustered one has resulted to better performance. Especially if there are other index candidates or index joins can be used. It may be worth trying.

Mycroft Holmes wrote:
The query retuens 3.4m rows from a base table of 16m with lots of linkages

Have you measured and separated the execution time versus time to deliver the records. A large part of the wall time consumed may be used for transferring the data (execution plan doesn't show that). Also that's quite big amount of rows to handle in a procedure. Perhaps there could be ways to combine the queries or rearrange the logic somehow (purely guessing here).

The need to optimize rises from a bad design.My articles[^]

Gamzun17-Dec-08 8:48

Gamzun

17-Dec-08 8:48

I've notice some function names start with :: or @@ or without both for eg; ::fn_Trace_Getinfo(), @@Identity(), Identity() what's the difference? why they are named differently?

stored procedure. [modified]

Talsaniya Jayesh17-Dec-08 5:00

Talsaniya Jayesh

17-Dec-08 5:00

Hi,

Is it possible to password protect sql server stored procedure so that other user can not access or execute it???

Thank you.
Jayesh Talsania

modified on Wednesday, December 17, 2008 11:42 AM

Re: stored procedure.

Wendelius17-Dec-08 6:01

Store Procedure parameter

17-Dec-08 6:01

If you want to restrict users from modifying the procedure or executing it, it's done by using privileges on users or roles (which then are granted to users). Have a look at GRANT[^] statement. If you don't grant sufficient privileges to a person, he won't be able to execute the procedure.

Note that user can have execute privileges even if he hasn't been granted them to the procedure directly. For example database owner has privileges to do anything in the database and so on.

The need to optimize rises from a bad design.My articles[^]

faizurrahman17-Dec-08 1:26

faizurrahman

17-Dec-08 1:26

I want to write a store procedure which accept anothor store procedure as parameter and then retrun the datatypes of input variables of given store procedure. Plz help if any one knows n how to do it.

Re: Store Procedure parameter

Ashfield17-Dec-08 2:27

Ashfield

17-Dec-08 2:27

You can pick up the definition of the stored proc from syscomments -

select text from syscomments where id = object_id('procname')

and concat all the comments together, then parse for the parameters.

Bob
Ashfield Consultants Ltd

Re: Store Procedure parameter

faizurrahman18-Dec-08 0:07

faizurrahman

18-Dec-08 0:07

Thank you I have got solution by a simple query:
select * from information_schema.Parameters where specific_Name = 'ProceName'

Re: Store Procedure parameter

Ashfield18-Dec-08 0:15

Ashfield

18-Dec-08 0:15

You learn something every day. I'll keep that view in mind. Smile | :)

Bob
Ashfield Consultants Ltd

Stupid INSERT question for PL-SQL

Nostrom016-Dec-08 8:58

16-Dec-08 8:58

I have two tables, TABLE_OLD and TABLE_NEW, like so:

TABLE_OLD
PART_NUM	Flag1	Flag2	Flag3
===============================
223		1	0	0
344		0	0	1
877		0	1	1
878		1	0	0

TABLE_NEW
PART_NO		Flag1	Flag2	Flag3
===============================
223		NULL	NULL	NULL
344		NULL	NULL	NULL
877		NULL	NULL	NULL
878		NULL	NULL	NULL

I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty.

Most if teh INSERT examples I have seen so far deal with simple inserts of hard-coded data, not this sort of situation. Is the query I want to run something like this?:

INSERT INTO 
	TABLE_NEW.Flag1
	TABLE_NEW.Flag2
	TABLE_NEW.Flag3
FROM 
	TABLE_OLD.Flag1
	TABLE_OLD.Flag2
	TABLE_OLD.Flag3
WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO;

Thanks for any help.

Wendelius16-Dec-08 9:12

16-Dec-08 9:12

Nostrom0 wrote:
I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty

If you want to insert new rows to the TABLE_NEW, it would be something like:

INSERT INTO TABLE_NEW (Flag1, Flag2, Flag3...)
SELECT Flag1, Flag2, Flag3...
FROM TABLE_OLD

If you want to restrict the rows that are inserted, add WHERE clause

However, I got the impression that you don't actually want to insert new rows, but to update already existing rows. If that's the case, then it would be like:

UPDATE TABLE_NEW
SET (Flag1, Flag2, Flag3...) = (SELECT Flag1, Flag2, Flag3...
                                FROM TABLE_OLD
                                WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO)

By the way, did you get the materialized view fixed?

The need to optimize rises from a bad design.My articles[^]

Nostrom016-Dec-08 10:35

16-Dec-08 10:35

Hi Mika, thank you for the answer.

I believe you are right - I do want to do an UPDATE statement, not an INSERT. Both the source and destination tables already have populate dpart number columns, and both have columns set for teh six flags. I just want to "pour" the data from the source to the destination by part number.

I have tried:

UPDATE  WEB_PRODUCT_TEST_COPY  p
SET     (
		CONTAINS_MARBLE_FLG, 
		IS_MARBLE_FLG, 
		CONTAINS_SMALL_BALL_FLG, 
		IS_SMALL_BALL_FLG, 
		SMALL_PARTS_FLG, 
		BALLOON_FLG
		) =
(SELECT  
		 CONTAINS_MARBLE_FLG, 
		 IS_MARBLE_FLG, 
		 CONTAINS_SMALL_BALL_FLG, 
		 IS_SMALL_BALL_FLG, 
		 SMALL_PARTS_FLG, 
		 BALLOON_FLG
 FROM    TEST_CPSC_BOOL
 WHERE   PART_NUM = p.PART_NO
);

...but I got an "ORA-01427: single-row subquery returns more than one row". I'm looking this up to see what I did wrong.

WEB_PRODUCT_TEST_COPY is the destination table, TEST_CPSC_BOOL is the source table, and the six columns ending in _FLG are Number fields set to 0 or 1. TEST_CPSC_BOOL uses PART_NUM for the part numbers, and WEB_PRODUCT_TEST_COPY uses PART_NO. If it makes any difference, I am using TOAD on an old 8i database...

Oh yes, I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step - i.e. if either the Proc or the MV try to reference columns that don't exist, they won't work.

Wendelius16-Dec-08 10:47

16-Dec-08 10:47

Nostrom0 wrote:
thank you for the answer

No problem.

Nostrom0 wrote:
I got an "ORA-01427: single-row subquery returns more than one row".

The reason is that the subquery that selects the data to be updated on WEB_PRODUCT_TEST_COPY returns several rows, meaning that you have several PART_NUM for a single p.PART_NO. Since the update is done for each row on a row basis, there must be one exact match in TEST_CPSC_BOOL (or no matches at all)

Nostrom0 wrote:
If it makes any difference, I am using TOAD on an old 8i database

That doesn't have any effect.

Nostrom0 wrote:
I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step

That sounds like a good plan.

The need to optimize rises from a bad design.My articles[^]

Nostrom016-Dec-08 11:20

16-Dec-08 11:20

Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL. I'll have to figure out away to find and kill them, or more likely find them in the original excel sheet the data was imported form and then re-import.

Wendelius16-Dec-08 11:27

16-Dec-08 11:27

Nostrom0 wrote:
Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL

If they are complete duplicates, meaning all the values are the same, you could try something like this:

UPDATE  WEB_PRODUCT_TEST_COPY  p
SET     (	
	CONTAINS_MARBLE_FLG, 	
	IS_MARBLE_FLG, 	
	CONTAINS_SMALL_BALL_FLG, 	
	IS_SMALL_BALL_FLG, 	
	SMALL_PARTS_FLG, 	
	BALLOON_FLG	
	)
 =(SELECT  	
	 CONTAINS_MARBLE_FLG, 	
	 IS_MARBLE_FLG, 	
	 CONTAINS_SMALL_BALL_FLG, 	
	 IS_SMALL_BALL_FLG, 	
	 SMALL_PARTS_FLG, 	
	 BALLOON_FLG
 FROM    TEST_CPSC_BOOL a1
 WHERE   PART_NUM = p.PART_NO
 AND     NOT EXISTS (SELECT 1
                     FROM TEST_CPSC_BOOL a2
                     WHERE a1.ROWID    < a2.ROWID
                     AND   a1.PART_NUM = a2.PART_NUM));

That should select only one of the duplicates, impossible to say which one, but if all of the values are the same, it wouldn't matter.

The need to optimize rises from a bad design.My articles[^]

Nostrom016-Dec-08 11:53

16-Dec-08 11:53

Wicked! That worked perfectly. Thank you for preventing me from spending the next three hours of my life screwing around with Conditional Formatting in Excel.

WEB_PRODUCT_TEST_COPY contains more rows/products than the list represented by TEST_CPSC_BOOL. Therefore, the products that don't need to be flagged, and were not included on the TEST_CPSC_BOOL list have a row of six NULLs instead of six 0's. I am hoping this won't make a difference - that when I complete the Proc that checks these new flag columns, my webapp will just treat the NULLs as 0's, not raise the corresponding flag, and not flip out. I suppose this is shading into more of an ASP.NET question than a PL_SQL question... I'll just try it out and see what happens. If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's...

Thanks again, Mika!

Wendelius16-Dec-08 12:05

16-Dec-08 12:05

Nostrom0 wrote:
Wicked! That worked perfectly

It's a kind of magic Poke tongue | ;-P

When you have time, have a look at ROWID column definition on SQL documentation for Oracle.

Nostrom0 wrote:
If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's

Have a look at NVL function. You can use it in the update statement (in the select portion).

Nostrom0 wrote:
Thanks again, Mika!

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]