This is n contrary to your reply Rgds Anil. Wrong plan May 01, - Hi Tom A bit dissapointed after seeing the inconsistancy.
I tried this query and got full scan. In LIst May 01, - Hi Tom Thanks for the suggestion. It is working consistently in 9i and 10g.
What is the impact of using temporary table, IO generattion. I had read some where in your site that you were saying, try to avoid using temp table.
Any comments Rgds Anil. Thanks Tom, One doubt about Temp table , when you said the data in the temp table does not hit the disk only cached, does temp table use buffer cache memory or PGA to store the data.
Single or multiple gtt May 04, - 3: Hi In our applicaition we have many places where awe use IN cluase to get child record. Is it better to use one global temporary table for different gtt for different queries.
Is there any impact?? Thanks for your suggestion Rgds Anil. Nested Tables and ODP. NET May 05, - 2: I'd like to perform SQL on a collection being passed into my package procedure as a parameter, so I made it a schema-level nested table type and cast it as you recommended in your initial response.
Is there any other way to accomplish what I want to do short of looping through the passed in associative array to populate my nested table? NET May 05, - 3: NET at this time.
I show a similar technique in Chapter 5 of my book - i. As Chris points out, it can be a bit of extra work. Thanks Mark May 10, - 3: Thanks for the reply, but that was me on OTN to whom Chris replied.
My post on OTN asked if anyone had any alternatives to looping through the Associative Array to populate a nested table. I was just hoping that he was giving me one or that someone else found one.
OTN Post May 10, - Hi Yuan, I had figured it was your post on the OTN forums, just wasn't sure if you had seen it yet or not. Object support is probably one of the most requested features for ODP right now.
Gabriel from Montreal, Canada. Hello Tom, The in list saved me a lot of time but I was wondering why not use reference cursors instead on in varchar2 parameters like so: Extension to array matching May 13, - 6: This link has been really helpful.
I have used some of things here to compare comma-delimited lists of strings, but I also need to compare wildcards within these strings. June 14, - 4: Andy from Hyderabad, India.
But any way here it goes I thought this might work But looking for a better solution if possible June 22, - 1: Matthias Rogel from Kaiserslautern, Germany.
I absolutely agree that "order siblings by" would "obviously" be considered "order by". June 24, - 7: I misunderstood the documenation?
Oracle returns the rows in the order shown in Figure In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure , "Hierarchical Tree".
I would say, since we don't have siblings here, and children appear below their parents, and we select only level, we'll get the numbers 1 2 3 4 5 Just "parent, followed by child, in some order" June 24, - 8: List item July 10, - 6: John Binny from Middle East.
Hi Tom How r U? I am working in Oracle JDeveloper 10g. I want to change an item into List Item. Jeremy from Lansing, MI.
Hey tom, just was looking over your str2tbl and i have two questions Would you see any benifit to declaring this function deterministic?
I know that this allows you to declare function-based indexes on it and use it in MV's, but could it also affect the plans generated by avoiding repeat runs of the function in the same statement, transaction, or session?
This is my REAL question Or even better - can I get the optomizer to look into the table and choose an appropriate plan based on it's contents?
Oops, now I'm the dolt who forgets to do a search before submitting a question. Thanks for the gracious link instead of just saying "RTFM" I'm really scratching my head on this one.
Secondly, from your example I'm almost dead positive that the reason STR2TBL was called times wasn't because it wasn't materialized, but because it was the outer rowsource in a nested loop join.
If you had a full table scan as the outer table then that scan would happen times too, wouldn't it? Were you on 9i when you did that example?
In fact it seems to make things worse. Appears to me that 10g is doing the opposite of your previous example!
When I get a chance I'll check out 9i, but I'm betting that the "VIEW" collection materialization isn't affecting how many times the procedure gets run so much as the plan -- maybe by materializing the subquery you made the CBO suddenly listen to the hint you have it and put the function on the inside of the nested loop.
Explode a column July 21, - Suppose I have a table like create table t pk int primary key, csv varchar2 ; insert into t values 1,'a,b,c' ; insert into t values 2,'x,y,z' ; Using the techniques described on this page, is there a way I can create a view on this table that "normalizes" the table i.
So if I have this view v, I would do select Explode a column July 21, - 5: Not sure I understand why you changed strtbl to be a pipelined function.
Can this be done without using pipelined functions? Explode a column July 21, - 8: Well, your original version of str2tbl presented on this site even for 9iR2 didnt have the "pipelined" and you suddenly added it in response to my question so I was wondering if it was "required" for things to work.
Would this work without the pipelined? Hi Tom, table t1 contains records for 'a' and 'b' in column col1 I have a IN string with values 'a','b','c','d' I would like to find which values contained in the IN clause are NOT present in the table column col1 e.
Try this July 25, - 5: Obviously it assumes comma delimited data. Connected to Oracle9i Enterprise Edition Release 9. I want to force an outline for query with query-1 is it possible?
My query is more complex like select a. September 15, - The Optimizer might be right With query-1 i see NL's. But one thing that is for sure is when i have a big variable inlist the optimizer takes a different plan.
September 15, - 1: September 15, - 3: Tom, Not sure whether you have seen this update: Faster version September 15, - 4: Ajay from Boston, MA.
For larger lists, the following statement is faster select substr: Ajay September 15, - 5: Ajay, you don't need the distinct.
Just don't produce anymore rows than you need: September 15, - 7: And thanks for the compliment, dnd. September 16, - 1: Hi Tom, Your answer is good.
Faster version September 16, - 9: You have a query from dual returning rows that did physical reads and 5 gets in current mode.
September 16, - Gabe, I'm not sure I understand what you mean by isolating the tests. To get a clean trace file, these two statements and setting the value of mystring were the only statements run in the session.
Gabe, I've taken suggestions for you, Ajay and Anders and applied them to my original version. Its much cleaner and seems to perform well.
Sorry I'm being a little dense. The input was 'x,y,z' The String y z Missing the 'x' row. September 16, - 3: Event waited on Times Max.
My explanation is that one of the inline view gets materialized and goes to disk. Missed 1 in the connect by clause September 16, - 3: Frank Zhou from Braintree, MA.
September 16, - 4: Is there a predictable threshold for when the materialization would start happening? Can it be influenced? I have written a pipe lined function to perform a variable in list that is essentially the same as what is listed in this thread.
However, when I use my inlist function instead of hardcoding a list of values, I am having trouble getting the sql to range scan an index instead of fast full scanning the index or simply performing a tablescan.
I have put together a small test case that appears below. I'll have a hard time getting developers to use bind variables for in-lists if I can't improve the performance of this method using bind variables.
December 22, - 9: Tom you are the man! When I applied this to a true application scenario for demonstration purposes, my version ran nearly 8X faster.
You just have to love those bind variables. It will be something like: Is it possible to execute this query in chunks of characters and store the results in a temp table or something similar?
But how do I define the chunks? The query field in table t can be characters! Kindly suggest an efficient way to do this. Intersection of queries March 23, - 6: Tom, Thanks for the reply.
This is for an 'Advanced Search' application being developed. User selects some parameters on screen and the resultant sql-query is passed to stored procedure to be stored in the database.
Then the same user selects another criteria and this also gets stored So they want all these queries to be stored and intersection of all these queries to be done by database procedures and a count returnd.
We're not inserting records, but only searching i. The inserts I wrote were to show sample data in the table and just for the purpose so that you could replicate the scenario easily please.
Actually, the users want those queries to be inserted in the table by the stored procedure. The application users opine that this is a very dynamic meathod -- since you're storing queries, so the count done at any time will be at that point of time.
Earlier we were thinking of storing the resultant seqnos as comma delimited CLOB instead of the queries -- but that didn't work out because the search on millions of seqnos as a CLOB was very slow, even after using context index They want two procedures like the following: ID is just a sequence number and is PK of the table t.
It is kind of a basic filter. They want me to concatenate the advanced search query to this sqlfilter as following: This database table will be used by Crystal Reports for reporting purposes.
Kindly advise how can I cater to the user requirements. March 24, - Hi Tom, I have the following requirement. I have a table t with structure as follows: ID 1 2 Now if you go back to my insert statements in the last one, I have assigned the value of col3 as 'Value for col1'.
In my current setup that is not possible because I am comparing pcol1 with col1, pcol2 with col2 and pcol3 with col3. Is there any way that I can change this a little bit such that I will only get one input parameter pcol and I should compare it against col1, col2 and col3 and return all matches so that when I execute my procedure I should 1, 2 and 5 as the results rather than just 1 and 2.
March 24, - 4: I am sorry, I did not follow you. Are you asking me a question? To be sure April 10, - Carl Bruneau from Montreal, Qc, Canada.
Hello Thomas, I read this thread but there is some things I am not sure to understand. Because if we don't use this approach we will have as many statement in the shared pool as we have possible combinations of element in the INLIST, rigth?
And it will not scale at all, rigth? One hard parse April 11, - 9: Hello Thomas, I finally verified and the answer seems to be Yes.
I traced many execution of the same generic statement: The other execution not: May 04, - 8: Recommendation to Oracle May 04, - RV from Every where.
The table variable in Sybase from very early version and carried to MS Sql Server is very handy for the database programmers. These functionality is available in Oracle in a complex way as explained in this article from Oracle 8 onwards; still it is complex.
One more trick May 05, - 6: Matthias Rogel from Kaiserslautern Germany. Tom from Philadelphia, PA. Hi Tom, Many thanks for all the excellent examples and great solutions.
The approach works fine except when the IN list exceeds characters. The list is truncated. As we can see the context holds the correct value ie, greater than characters we assume that there is actually no limitation on the attribute value other than it must be a VARCHAR2.
Would you be able to explain why the value is truncated when the SQL is executed? We have to use dynamic sql as there are 17 fields which the user can search on or any combination of those 17 fields.
When the code is run the session context attribute value is the full string but when the sql is executed the value is truncated at characters. Thank you for your assistance.
June 01, - Steve Standish from Boston, MA. Hi Tom, Hopefully this is a simple mistake or mis-use: Steve from Boston, MA.
Hi Tom, thanks for your time. I provided the simplest example of the problem. Here I get the same problem?
Function created with compilation errors. OK, thanks, I get it now! Although this original expample is simple my intent was to encapsulate the types into the package not declare stand alone so I could make my package types like: This way I wouldn't have alot of standalone "types" sitting around in SQL.
If there is a way to do something like this then great Just to clarify June 01, - 1: Sorry, I think my statment was not clear: This is simply a code management approach.
This method allows for calling components to change versions when ready. Oracle won't understand a type definition in a package? Good Stuff June 01, - 3: It works and gives me what I want see below , thanks.
This type of thing will be incorporated into larger quieries. Any words of wisdom on performance when using this type of function as part of a larger SQL?
For COST there would be no stats on a returning function? In our simple example here could this be a deterministic function?
Excuse me as I have posted the same Qs in different loactions,but this the proper place to post June 02, - Is it unable to understand the lists in the "in" clause?
It was actually a Form 6i Trigger code for the control item: August 04, - 1: September 14, - Check for existance of Object before proceeding September 21, - If the table is not there for the 2nd value, then i just waste time for the 1st processing.
I want to check if objects exists for all values of select clause and if they are, then only proceed to the next. September 22, - 9: Apologize for the confusion.
Once it finds the table, then it proceeds ahead for doing further validations and insertions. What if it does not find the table during the 2nd loop.
It would do all the processing, validations for the 1st value in the loop and then proceed for the 2nd value in the loop and then generate exceptions.
September 22, - 5: Well it hurts much more because this table is a from a customized application that our client has and unfortunately it is going to remain the same in the near future.
So partitioning is ruled out. Supposing the distinct values for years are 03,04 and If yes, then proceed with the backups, if no then raise the flag.
This is the snipet of the code. September 25, - Here is the entire code. This code works and creates the copy of the table one by one for each value of loop j.
So where do i put in this exception logic. Confused September 28, - 5: Shankar Ram from Austin. Hi, I'm learning this new concept of pipelined functions.
I have a scenario. STEP 1 - select query is generated and a list of addresses are resulted. STEP 2 pipelined function is created and stores the value of this result.
STEP 3 Stored addresses are checked and status columns is updated for each address. How do I reset the pipelined functions. Is this scenario requires pipelined functions?
Do you have any other suggestion on this. November 03, - 6: A reader from India. This actually worked for me and so I am really curious.
Please bear with me as I am a bit of a newbie. Raj from bristol, USA. I don't know what is going on wrong.. But it is not happening.
Is something which I am missing. For one value it is working properly. When I am doing concatenation it is inserting zero rows. Environment is Oracle 9i 9.
Hi Tom, Thanks for your recommendation. Reply would be appreciated. Using collection in SQL January 30, - 2: Sunj from Plainfield, NJ.
Tom, My question is related, but I couldn't find my specific answer after searching through this and related threads.
Why is the difference in performance, eventhough the result is same? Using collection in SQL February 01, - Tom, Thanks for your insight.
I compared the PLANs, there's big difference in cardinality. Another February 02, - 8: Venkat from Pittsburgh PA. Sunj, There is also one more way in Oracle you can achieve this..
I dont know how would it suit your requirement but if you have 10G Using collection in SQL February 06, - 2: Tom, Thank you for interpreting the plan for me in detail.
Much appreciate your time. Can I use hierarchichal query for this? May 08, - Number of characters in S1 and S2 are always the same for particular record.
I'm trying to build query that returns this: I was able to implement it easily with pipe function that parses every input record and returns result records.
But I'd like to have it done by this elegant hierarchichal query solution using level etc. The reason is trying to stay in SQL - while processing couple of millions of records like this and real structure is more complicated performance is not acceptable: I tried, but failed.
May 11, - But unfortunately this level can be as deep as which actually increased processing time in comparison with pipe function: Heartfelt thanks and a pointer for others June 26, - 6: Stew Ashton from Paris, France.
Tom, I have used your "varying in list" solutions to great effect, but I had big problems with a complex query: Used together, they get me excellent plans and response times.
A permutation of the integer 0 to N-1 corresponds to a placement of queens on an N-by-N chessboard so that no two queens are in the same row or column.
Write a program QueensChecker. A large number of college students are attending a party. Each guest is drinking a can of beer or soda of they are under An emergency causes the lights to go out and the fire alarm to go off.
The guests calmly put down their beer and exit the building. When the alarm goes off, they re-enter and try to retrieve their beer. However, the lights are still off, so each student randomly grabs a bottle of beer.
What are the chances that at least one student gets his or her original beer? Write a program MyBeer. Print the fraction of times that at least one guest gets their original beer.
As n gets large, does this fraction approach 0 or 1 or something in between? Linear feedback shift register. Rewrite linear feedback shift register from Chapter 1 by using an array to streamline it and makes it more extensible, e.
Your are in a locker room with open lockers, numbered 1 to Toggle all of the lockers that are even. By toggle , we mean close if it is open, and open if it is closed.
Now toggle all of the lockers that are multiples of three. Repeat with multiples of 4, 5, up to How many lockers are open? Guess you don't need an array once you see the pattern.
Suppose that you have N tasks to schedule. Each task takes 1 unit of time and has a deadline by which time it is expected to finish. Find a schedule that minimizes the penalty.
Use arrays to store the names of the days of the week, the names of the months, and the number of days in a month.
Given an N-by-N grid with each cell either occupied by an 'X', an 'O', or empty, write a program to find the longest sequence of consecutive 'X's either horizontal, vertically, or diagonally.
Write a program KickBoxer. Write a program that counts in base N from 0 to N 20 - 1. Use an array of 20 elements. Given an N-by-N grid of elevation values in meters , a peak is a grid point for which all four neighboring cells are strictly lower.
Write a code fragment that counts the number of peaks in a given N-by-N grid. Write a program MagicSquare. Repeatedly assign the next integer to the cell adjacent diagonally to the right and down.
If this cell has already been assigned another integer, instead use the cell adjacently above. Use wrap-around to handle border cases.
Write a program Banner. Voting and social choice theory. Plurality US presidential election , run-off elections, sequential run-off elections Australia, Ireland, Princeton faculty committees , Condorcet.
Same ideas for sports, google, meta-search, machine learning Borda count. In , Borda proposed a positional method for determining the outcome of a political election with K voters and N candidates.
Each voter ranks the candidates in increasing order of preference from 1 to N. Borda's method assigns a score to each candidate equal to the sum of their rankings.
The candidate with the highest sum wins. Given two permutations, Kendall's tau distance is the number of pairs out of position.
Optimal Kemeny rank aggregation in voting theory minimizes Kendall tau distance. Also useful for ranking genes using several expression profiles, ranking search engine results, etc.
Given two permutations, Spearman's footrule distance is the L1 distance between the permutations as vectors.
Useful in top-k lists. Each decimal digit in the zip code is encoded using a sequence of 5 short and long lines for use by scanners as follows: In addition, a long line is added to the beginning and appended to the end.
Write a program ZipBarCoder. Print the code vertically instead of horizontally, e. Repeat the previous exercise, but plot the output using Turtle graphics.
Gaps with no primes. Find the longest consecutive sequence of integers with no primes. Write a program PrimeGap.
In , Christian Goldbach conjectured that every even number greater than 2 could be written as the sum of two primes.
Write a program Goldbach. Goldbach's conjecture is still unresolved, but it is known to be true for all N Write a program that takes an integer n from the command line, generates a random permutation, prints the permutation, and prints the number of left-to-right minima in the permutation the number of times an element is the smallest seen so far.
Then write a program that takes integers m and n from the command line, generates m random permutations of length n, and prints the average number of left-to-right minima in the permutations generated.
Formulate a hypothesis about the number of left-to-right minima in a permutation of length n, as a function of n.
Alice and Bob are in a heated argument about whether if they repeatedly roll a die until the sum is more than 12, is 13 the most likely sum? Write a program MostLikelyRoll.
Given a 2-D array, write a program Spiral. Given a 9-by-9 array of integers between 1 and 9, check if it is a valid solution to a Sudoku puzzle: Evaluate how much time this saves.
If you want to learn more about parallel programming, please check here. Now Debugging the parallel program is also a big topic.
Here I will give you a basic overview to know about the debugging of parallel program. To discuss about it, let's consider you have the following piece of code:.
To understand the parallel program debugging, we need to be aware about two window options: Before continuing with parallel tasks and parallel stacks, you have to know about Threads Window which I have already covered.
In the given code, you have three different tasks which are doing something and after sometime, all the tasks are put on hold. This is done intentionally to check the status of each task.
To test, put a breakpoint on DoSomeWork method and run the application. You will see your program execution paused on the breakpoint.
I asked to open both at the same time only because you can visualize what is going on. Parallel Task window will show you what are the different tasks that have been created for the program and what is their current status.
On the other hand, Parallel Stacks will show you the graphical view of all thread creation, containing tasks, how they are related.
If you click on the thread from the Parallel Stacks, it will show you the code line related with the thread as shown in the picture with a Green Arrow.
To move ahead, press F5. Let's see what comes next. In the above diagram, you can find one of the tasks has been executed and the other two are remaining.
These topics is very interesting and long to discuss. Please check further study section of the article to know more details.
IntelliTrace Debugging is sometimes called as historical Debugging. IntelliTrace operates in the background, records what you are doing during debugging.
When you want the information of previous event or some particular event, you can easily get it from intelliTrace information, a past state of your application.
In this mode, you can navigate to various events, steps that are recorded. In this section, I will give you a basic overview of how to use IntelliTrace.
Here I am using one sample program by which I will show you what IntelliTrace does. Below is the sample code block:.
Did you find any relationship with your code and with the IntelliTracker List view? I guess you should. If not, let me explain about it.
Let's have a look into the below picture:. From the picture, I am sure you have got the idea what IntelliTrace is doing.
If you call a method, it will capture. If code fired an event, it will trap. Yes at a single statement, I will capture each and everything.
If you can get the call stack, Local variables information are recorded. When you will select any block, it will expand automatically and that line will be highlighted.
As per the given picture, I have highlighted the first console section and you can see the corresponding code block has also been highlight.
You can now easily map them. I have already explained Locals and Stack Calls. But can you imagine what is the use of Locals and Call Stack over here.
Yes, you guessed it correctly, it will show you the Locals and Call Stack status for that time period when the selected block has been executed.
What a nice feature this is. If you have a lot of recorded information in the intellitrace window, you can easily filter them out. You can filter data based on the Categories or Thread.
As for example suppose you want to see only the data that is related with Console Related, you just need to check the " Console " From categories list.
You may change the location. Debugging with IntelliTrace is itself a big topic. So it is very much difficult for me to cover them up within this article.
I just give you the basic overview so that you can at least explore it by yourself now. Please check the further study section to know more about it.
I am stopping here. Hope you have enjoyed the full article. Please share your feedback and suggestions. This article covers basic fundamentals of debugging procedure.
I have explained almost all important tools and their uses. For Parallel program debugging, I have covered only basics.
In further study section, there is a great article on Parallel debugging procedure. If you are interested, please go through the link.
My main objective was to cover almost all utilities that are available in Visual Studio debugging. Hope you have learned something new from this article.
Articles Quick Answers Messages. Abhijit J ana , 6 May Please Sign up or sign in to vote. Table of Contents Introduction How to Start? AbhijitJana My Kinect Book: Azure Search - a quick overview.
Adding and extracting binary resources. Scaffolding TypeScript with CatFactory. Remote debugging with Visual Studio Member May Karthik Bangalore 7-Mar Praveen P N Oct 4: Amir Mohammad Nasrollahi Aug Kumar nanda Apr 0: Oshtri Deka Apr 0: Ashu Pandey 9-Oct 1: Terminates the current debugging session, rebuilds if necessary, and starts a new debugging session.
Starts or resumes execution of your code and then halts execution when it reaches the selected statement. If not currently debugging, this runs the startup project or projects and attaches the debugger.
Displays the Processes dialog, which allows you to attach or detach the debugger to one or more running processes. Very helpful, even for a veteran programmer.
Thanks Member May Thank you,this article was the most useful one I've ever read and I think that saved my time a lot.
My vote of 5 Karthik Bangalore 7-Mar Update it for VS devenv. Data Breakpoint Jul 3: Add description about data breakpoint.
Awesome and very useful article, also very nice demonstration. If you have time please extend this tutorial to make it a series of articles.
My vote of 5 Amir Mohammad Nasrollahi Aug This would be wonderful, more than it already is, to have a way to explain the check-in process of TFS in relation to setting breakpoints for all developers of a domain.
I would appreciate an overview of how one could manage a team through breakpoint process and relate this to scrum management of time would be a world of help.
Profoundly covers the topic. My vote of 5 hebsiboy Jul My vote of 5 Rajibgiri. My vote of 5 Kumar nanda Apr 0: My vote of 5 Oshtri Deka Apr 0: My vote of 5 amirrazzaqi Jan 3: Super meorfi Dec Concise and instructive well written.
Very Useful for Beginners narayanakv Nov It's nice and useful to beginners a lot.