Search Results

Search found 25600 results on 1024 pages for 'table partitioning'.

Page 221/1024 | < Previous Page | 217 218 219 220 221 222 223 224 225 226 227 228  | Next Page >

  • In an Android application, should I have one content provider per table or only one for the entire a

    - by Andrew Dyer
    I have years of experience with Microsoft .NET development (primarily C#) and have been working to come up to speed on Android and Java. So far, I've built a small application with a couple screens and a working content provider. All of the examples I've seen for developing content providers typically work with a single table, so I got the impression that this was the convention. I built a couple more content providers for other tables and ran into the "Unknown URI" IllegalArgumentException when I tried to test them. The exception is being thrown by one of my content providers, but not the one I was intending to call. It appears that my application is using the first content provider in the AndroidManifest.xml file, which now has me wondering if I should only have a single content provider for the entire application. Are there any best practices and/or examples for working with multiple tables in an Android application? Should I have one content provider per table or only one for the entire application? If the former, how do I resolve URIs to the proper provider? If the latter, how do I keep my content provider code from being polluted with switch statements?

    Read the article

  • Ways to implement tags - pros and cons of each

    - by bobobobo
    Related Using SO as an example, what is the most sensible way to manage tags if you anticipate they will change often? Way 1: Seriously denormalized (comma delimited) table posts +--------+-----------------+ | postId | tags | +--------+-----------------+ | 1 | c++,search,code | Here tags are comma delimited. Pros: Tags are retrieved at once with a single select query. Updating tags is simple. Easy and cheap to update. Cons: Extra parsing on tag retrieval, difficult to count how many posts use which tags. (alternatively, if limited to something like 5 tags) table posts +--------+-------+-------+-------+-------+-------+ | postId | tag_1 | tag_2 | tag_3 | tag_4 | tag_5 | +--------+-------+-------+-------+-------+-------+ | 1 | c++ |search | code | | | Way 2: "Slightly normalized" (separate table, no intersection) table posts +--------+-------------------+ | postId | title | +--------+-------------------+ | 1 | How do u tag? | table taggings +--------+---------+ | postId | tagName | +--------+---------+ | 1 | C++ | | 1 | search | Pros: Easy to see tag counts (count(*) from taggings where tagName='C++'). Cons: tagName will likely be repeated many, many times. Way 3: The cool kid's (normalized with intersection table) table posts +--------+---------------------------------------+ | postId | title | +--------+---------------------------------------+ | 1 | Why is a raven like a writing desk? | table tags +--------+---------+ | tagId | tagName | +--------+---------+ | 1 | C++ | | 2 | search | | 3 | foofle | table taggings +--------+---------+ | postId | tagId | +--------+---------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | Pros: No repeating tag names. More girls will like you. Cons: More expensive to change tags than way #1.

    Read the article

  • GridView ObjectDataSource LINQ Paging and Sorting using multiple table query.

    - by user367426
    I am trying to create a pageing and sorting object data source that before execution returns all results, then sorts on these results before filtering and then using the take and skip methods with the aim of retrieving just a subset of results from the database (saving on database traffic). this is based on the following article: http://www.singingeels.com/Blogs/Nullable/2008/03/26/Dynamic_LINQ_OrderBy_using_String_Names.aspx Now I have managed to get this working even creating lambda expressions to reflect the sort expression returned from the grid even finding out the data type to sort for DateTime and Decimal. public static string GetReturnType<TInput>(string value) { var param = Expression.Parameter(typeof(TInput), "o"); Expression a = Expression.Property(param, "DisplayPriceType"); Expression b = Expression.Property(a, "Name"); Expression converted = Expression.Convert(Expression.Property(param, value), typeof(object)); Expression<Func<TInput, object>> mySortExpression = Expression.Lambda<Func<TInput, object>>(converted, param); UnaryExpression member = (UnaryExpression)mySortExpression.Body; return member.Operand.Type.FullName; } Now the problem I have is that many of the Queries return joined tables and I would like to sort on fields from the other tables. So when executing a query you can create a function that will assign the properties from other tables to properties created in the partial class. public static Account InitAccount(Account account) { account.CurrencyName = account.Currency.Name; account.PriceTypeName = account.DisplayPriceType.Name; return account; } So my question is, is there a way to assign the value from the joined table to the property of the current table partial class? i have tried using. from a in dc.Accounts where a.CompanyID == companyID && a.Archived == null select new { PriceTypeName = a.DisplayPriceType.Name}) but this seems to mess up my SortExpression. Any help on this would be much appreciated, I do understand that this is complex stuff.

    Read the article

  • Using placeholders/variables in a sed command

    - by jesse_galley
    I want to store a specific part of a matched result as a variable to be used for replacement later. I would like to keep this in a one liner instead of finding the variable I need before hand. when configuring apache, and use mod_rewrite, you can specificy specific parts of patterns to be used as variables,like this: RewriteRule ^www.example.com/page/(.*)$ http://www.example.com/page.php?page=$1 [R=301,L] the part of the pattern match that's contained inside the parenthesis is stored as $1 for use later. So if the url was www.example.com/page/home, it would be replaced with www.example.com/page.php?page=home. So the "home" part of the match was saved in $1 because it was the part of the pattern inside the parenthesis. I want something like this functionality with a sed command, I need to automatically replace many strings in a SQL dump file, to add drop table if exist commands before each create table, but I need to know the table name to do this, so if the dump file contains something like: ... CREATE TABLE `orders` ... I need to run something like: cat dump.sql | sed "s/CREATE TABLE `(.*)`/DROP TABLE IF EXISTS $1\N CREATE TABLE `$1`/g" to get the result of: ... DROP TABLE IF EXISTS `orders` CREATE TABLE `orders` ... I'm using the mod_rewrite syntax in the sed command as a logical example of what I'm trying to do. Any suggestions?

    Read the article

  • How to duplicate all data in a table except for a single column that should be changed.

    - by twiga
    I have a question regarding a unified insert query against tables with different data structures (Oracle). Let me elaborate with an example: tb_customers ( id NUMBER(3), name VARCHAR2(40), archive_id NUMBER(3) ) tb_suppliers ( id NUMBER(3), name VARCHAR2(40), contact VARCHAR2(40), xxx, xxx, archive_id NUMBER(3) ) The only column that is present in all tables is [archive_id]. The plan is to create a new archive of the dataset by copying (duplicating) all records to a different database partition and incrementing the archive_id for those records accordingly. [archive_id] is always part of the primary key. My problem is with select statements to do the actual duplication of the data. Because the columns are variable, I am struggling to come up with a unified select statement that will copy the data and update the archive_id. One solution (that works), is to iterate over all the tables in a stored procedure and do a: CREATE TABLE temp as (SELECT * from ORIGINAL_TABLE); UPDATE temp SET archive_id=something; INSERT INTO temp (select * from temp); DROP TABLE temp; I do not like this solution very much as the DDL commands muck up all restore points. Does anyone else have any solution?

    Read the article

  • how to decrease queries in php/mysql array selection loop

    - by Mac Taylor
    hey guys i need to show stories details and tags' names in my php/mysql project . for every story row, there is a filed named : tags that save tags id as an array Table name: stories table filed : tags example of tags filed : 1 5 6 space between them and i have a tag table that looks like this Table name : bt_tags Table fileds : tid,tag now problem : when using while loop to fetch all fields in story table , the page uses 1 query to show every stories' detail but for showing tag's names , i should query another table to find names , we have ids stored in story table now i used for loop between while loop to show tag names but im sure there is a better way to decrease page queries $result = $db->sql_query("SELECT * FROM ".STORY_TABLE." "); while ($row = $db->sql_fetchrow($result)) { //fetching other $vars ---- $tags_id = explode(" ",$row['tags']); $c = count($tags_id); for($i=1;$i<$c-1;$i++){ list($tag_name,$slug) = $db->sql_fetchrow($db->sql_query( 'SELECT `tag`,`slug` FROM `bt_tags` WHERE `tid` = "'.tags_id[$i].'" LIMIT 1' )); $sow_tags = '$tag_name,'; } im not allowed to change anything in database table how can i improve this script and show tag's names without using *for loop ?*

    Read the article

  • Passing user_id, site_id, and question_id to same table on create...

    - by bgadoci
    I can't seem to figure out how to do this. I am trying to pass four different variables to a single table. To be more specific I am trying to create a "like" in a likes table that also captures the site_id (like an answer), user_id, and the question_id. Here is the set up. class Like < ActiveRecord::Base belongs_to :site belongs_to :user belongs_to :question end I will spare you the reverse, has_many associations but they are there. Here is the likes controller where I think the problem is. class LikesController < ApplicationController def create @user = current_user @site = Site.find(params[:site_id]) @like = @site.likes.create!(params[:like]) @like.user = current_user @like.save respond_to do |format| format.html { redirect_to @site} format.js end end end This code successfully passes the like and site_id but after many different variations of trying I can't get it to pass the question id. Here is my form: /views/sites/_site.html.erb (though the partial is being displayed in the /views/questions/show.html.erb file). <% remote_form_for [site, Like.new] do |f| %> <%= f.hidden_field :site_name, :value => "#{site.name}" %> <%= f.hidden_field :ip_address, :value => "#{request.remote_ip}" %> <%= f.hidden_field :like, :value => "1" %> <%= submit_tag "^" , :class => 'voteup' %> <% end %>

    Read the article

  • How to create a tabbar on clicking a row in table in iphone?

    - by Warrior
    My first view consist of table with 7 rows of data.When i click each row it should navigate to a view with tab bar with 4 tabitems .In this what is the proper approac of creating a tab bar. When should i create it.Should i create in didSelectRowAtIndexPath of the table in first view like this UITabBarController *tabbar=[[UITabBarController alloc] init]; Homepageview* vc1 = [[Homepageview alloc] initWithNibName:@"Homepageview" bundle:nil] ; vc1.tabBarItem.title=@"Home"; vc1.tabBarItem.image = [UIImage imageNamed:@"home.png"]; ShoppingView* vc2 = [[ShoppingView alloc] initWithNibName:@"ShoppingView" bundle:nil]; vc2.tabBarItem.title=@"Shopping"; vc2.tabBarItem.image = [UIImage imageNamed:@"shopping.png"]; Checkout* vc3 = [[Checkout alloc] initWithNibName:@"Checkout" bundle:nil] ; vc3.tabBarItem.title=@"Checkout"; vc3.tabBarItem.image = [UIImage imageNamed:@"checkout1.png"]; Settings *vc4=[[SlSettings alloc] initWithNibName:@"Settings" bundle:nil] ; vc4.tabBarItem.title=@"Settings"; vc4.tabBarItem.image = [UIImage imageNamed:@"setting1.png"]; tabbar.delegate = self; tabbar.viewControllers = [NSArray arrayWithObjects:vc1, vc2, vc3,vc4,nil]; //self.navigationItem.title=@"Snhopping list"; //[tabbar.selectedViewController viewDidAppear:YES]; [self.navigationController pushViewController:tabbar animated:YES]; [vc1 release]; [vc2 release]; [vc3 release]; [vc4 release]; [tabbar release]; Or is there any alternate to do it. If i do it by the above method , when i navigate from the home page view of tabbar to another view then i am not able to get tab bar.Please help me out.Thanks.

    Read the article

  • object references an unsaved transient instance

    - by developer
    Hi, I have 2 tables, user and userprofile, both with almost identical fields. user table references userprofile table by primary key ID. My requirement is that on click of a button I need to dump user table record to userprofile table. Now for a particular user table, if there is a corresponding userprofile entry, I am successfully able to dump the data, but if there is no record in userprofile table then I need to create a new record by dumping all the data. My problem is that I am able to update the data when the record is present in userprofile table, but in the case wherein I have to create a new record I get the below error "object references an unsaved transient instance - save the transient instance before flushing". `<class name="User"> <id name="ID" type="Int32"> <generator class="native" /> </id> <many-to-one name="Pid" class="UserProfile" /> </class>` UserProfile is another table and Pid above references the Primary key ID of UserProfile table.

    Read the article

  • How can I pivot these key+values rows into a table of complete entries?

    - by CodexArcanum
    Maybe I demand too much from SQL but I feel like this should be possible. I start with a list of key-value pairs, like this: '0:First, 1:Second, 2:Third, 3:Fourth' etc. I can split this up pretty easily with a two-step parse that gets me a table like: EntryNumber PairNumber Item 0 0 0 1 0 First 2 1 1 3 1 Second etc. Now, in the simple case of splitting the pairs into a pair of columns, it's fairly easy. I'm interested in the more advanced case where I might have multiple values per entry, like: '0:First:Fishing, 1:Second:Camping, 2:Third:Hiking' and such. In that generic case, I'd like to find a way to take my 3-column result table and somehow pivot it to have one row per entry and one column per value-part. So I want to turn this: EntryNumber PairNumber Item 0 0 0 1 0 First 2 0 Fishing 3 1 1 4 1 Second 5 1 Camping Into this: Entry [1] [2] [3] 0 0 First Fishing 1 1 Second Camping Is that just too much for SQL to handle, or is there a way? Pivots (even tricky dynamic pivots) seem like an answer, but I can't figure how to get that to work.

    Read the article

  • How do I select column(s) by their "numeric" position in a table?

    - by DulcimerDude
    I am trying to select columns by their "x" position in the table. DBI my $example = $hookup->prepare(qq{SELECT This,That,Condition,"I also want COLUMN-10" FROM tbl LIMIT ? ?}); ###column_number=10 ordinal_position?? $example->execute('2','10') or die "Did not execute"; Is this possible or do I need to run another single select to just that column? One problem I encountered was with a col named "Condition". For some reason, when I tried to select Condition the execute would die. I never attempted but, What if the column name was SELECT? Another note is the table is 75 cols wide and I only need 50 of them. The Col names are pretty verbose so, I would like to just call them by their "position". This would also allow the col names to be changed in the future without having to change the select statement. I am quite the newbie so please explain any answers down to my level. Thanks for any assistance..

    Read the article

  • How would i output a message that says "values inserted to table" ?

    - by ranlo
    <?php error_reporting(0); $con=mysql_connect("localhost","root",""); if (!$con) { die('could not connect:'.mysql_error()); } mysql_select_db("final?orgdocs",$con); $org_name = $_POST["org_name"]; $org_type = $_POST["org_type"]; $org_code = $_POST["org_code"]; $description = $_POST["description"]; $stmt = "INSERT INTO organization VALUES('".$org_name."','".$org_type."','".$org_code."','".$description."')"; $result = mysql_query("SELECT * FROM organization WHERE org_name = '$org_name' "); echo '<TABLE BORDER = "1">'; $result1 = $result; while ($row = mysql_fetch_array($result1)){ echo '<TR>'.'<TD>'.'Organization Name'.'</TD>'.'<TD>'.'Organization Type'.'</TD>'.'<TD>'.'Organization Code'.'</TD>'.'<TD>'.'Description'.'</TD>'.'<TD>'.'Constitution'.'</TD>'; echo '</TR>'; echo '<TR>'.'<TD>'.$row['org_name'].'</TD>'.'<TD>'.$row['org_type'].'</TD>'; echo '<TD>'.$row['org_code'].'</TD>'.'<TD>'.$row['description'].'</TD>'.'<TD>'; echo '</TR>'; } echo '</TABLE>'; ?>

    Read the article

  • How can I pull multiple rows from a MySQL table and use all of them automatically for the same thing

    - by Rob
    Basically, I have multiple URL's stored in a MySQL table. I want to pull those URLs from the table and have cURL connect to all of them. Currently I've been storing the URL's in the local script, but I've added a new page that I can add and remove them from the database, and I'd like the page to reflect it appropriately. Here is what I currently have: $sites[0]['url'] = "http://example0.com "; $sites[1]['url'] = "http://example1.com"; $sites[2]['url'] = "http://example2.com"; $sites[3]['url'] = "http://example3.com"; foreach($sites as $s) { // Now for some cURL to run it. $ch = curl_init($s['url']); //load the urls and send GET data curl_setopt($ch, CURLOPT_TIMEOUT, 2); //No need to wait for it to load. Execute it and go. curl_exec($ch); //Execute curl_close($ch); //Close it off } Now I assume it can't be too amazingly difficult to do, I just don't know how. So if you could point me in the right direction, I'd be grateful. But if you supply me with some code, please comment it appropriately so that I can understand what each line is doing.

    Read the article

  • How to check dates don't overlap in a table using TSQL.

    - by Jon
    I have a table with start and finish datetimes that I need to determine if any overlap and not quite sure the best way to go. Initially I was thinking of using a nested cursor as shown below which does work, however I'm checking the same records against each other twice and I'm sure it is not very efficient. eg: this table would result in an overlap. id start end ------------------------------------------------------- 1 2009-10-22 10:19:00.000 2009-10-22 11:40:00.000 2 2009-10-22 10:31:00.000 2009-10-22 13:34:00.000 3 2009-10-22 16:31:00.000 2009-10-22 17:34:00.000 Declare @Start datetime, @End datetime, @OtherStart datetime, @OtherEnd datetime, @id int, @endCheck bit Set @endCheck = 0 DECLARE Cur1 CURSOR FOR select id, start, end from table1 OPEN Cur1 FETCH NEXT FROM Cur1 INTO @id, @Start, @End WHILE @@FETCH_STATUS = 0 AND @endCheck = 0 BEGIN -- Get a cursor on all the other records DECLARE Cur2 CURSOR FOR select start, end from table1 and id != @id OPEN Cur2 FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd WHILE @@FETCH_STATUS = 0 AND @endCheck = 0 BEGIN if ( @Start > @OtherStart AND @Start < @OtherEnd OR @End > @OtherStart AND @End < @OtherEnd ) or ( @OtherStart > @Start AND @OtherStart < @End OR @OtherEnd > @Start AND @OtherEnd < @End ) BEGIN SET @endCheck = 1 END FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd END CLOSE Cur2 DEALLOCATE Cur2 FETCH NEXT FROM Cur1 INTO @id, @Start, @End END CLOSE Cur1 DEALLOCATE Cur1

    Read the article

  • Create an index only on certain rows in mysql

    - by dhruvbird
    So, I have this funny requirement of creating an index on a table only on a certain set of rows. This is what my table looks like: USER: userid, friendid, created, blah0, blah1, ..., blahN Now, I'd like to create an index on: (userid, friendid, created) but only on those rows where userid = friendid. The reason being that this index is only going to be used to satisfy queries where the WHERE clause contains "userid = friendid". There will be many rows where this is NOT the case, and I really don't want to waste all that extra space on the index. Another option would be to create a table (query table) which is populated on insert/update of this table and create a trigger to do so, but again I am guessing an index on that table would mean that the data would be stored twice. How does mysql store Primary Keys? I mean is the table ordered on the Primary Key or is it ordered by insert order and the PK is like a normal unique index? I checked up on clustered indexes (http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html), but it seems only InnoDB supports them. I am using MyISAM (I mention this because then I could have created a clustered index on these 3 fields in the query table). I am basically looking for something like this: ALTER TABLE USERS ADD INDEX (userid, friendid, created) WHERE userid=friendid

    Read the article

  • ( Sql Server 2005 C#.Net ) - I want just the insert query for a temp table.

    - by John Stephen
    Hi..I am using C#.Net and Sql Server ( Windows Application ). I had created a temporary table. When a button is clicked, temporary table (#tmp_emp_details) is created. I am having another button called "insert Values" and also 5 textboxes. The values that are entered in the textbox are used and whenever com.ExecuteNonQuery(); line comes, it throws an error message called "Invalid object name '#tbl_emp_answer'.". Below is the set of code..Please give me a solution. Code for insert (in insert value button): private void btninsertvalues_Click(object sender, EventArgs e) { username = txtusername.Text; examloginid = txtexamloginid.Text; question = txtquestion.Text; answer = txtanswer.Text; useranswer = txtanswer.Text; SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"); SqlCommand com = new SqlCommand("Insert into #tbl_emp_answer values('"+username+"','"+examloginid+"','"+question+"','"+answer+"','"+useranswer+"')", con); con.Open(); com.ExecuteNonQuery(); con.Close(); }

    Read the article

  • Data base design with Blob

    - by mmuthu
    Hi, I have a situation where i need to store the binary data into database as blob column. There are three different table exists in my database where in i need to store a blob data for each record. Not every record will have the blob data all the time. It is time and user based. The table one will have to store the *.doc files almost for all the record The table two will have to store the *.xml optionally. The table three will have to store images (not sure what is frequency, etc) Now my questions is whether it is a good idea to maintain a separate table to store the blob data pointing it to the respective table PK's (Yes, there will be no FK's and assuming program will maintain it). It will be some thing like below, BLOB|PK_ID|TABLE_NAME Alternatively, is it a good idea to keep the blob column in respective tables. As for as my application runtime is concerned, The table 2 will be read very frequently. Though the blob column will not be required. The table 2 record will gets deleted frequently. Similarly other blob data in respective table will not be accessed frequently. All of the blob content will be read on-demand basis. I'm thinking first approach will work better for me. What do you guys think? Btw, I'm using Oracle.

    Read the article

  • Implementing search functionality with multiple optional parameters against database table.

    - by quarkX
    Hello, I would like to check if there is a preferred design pattern for implementing search functionality with multiple optional parameters against database table where the access to the database should be only via stored procedures. The targeted platform is .Net with SQL 2005, 2008 backend, but I think this is pretty generic problem. For example, we have customer table and we want to provide search functionality to the UI for different parameters, like customer Type, customer State, customer Zip, etc., and all of them are optional and can be selected in any combinations. In other words, the user can search by customerType only or by customerType, customerZIp or any other possible combinations. There are several available design approaches, but all of them have some disadvantages and I would like to ask if there is a preferred design among them or if there is another approach. Generate sql where clause sql statement dynamically in the business tier, based on the search request from the UI, and pass it to a stored procedure as parameter. Something like @Where = ‘where CustomerZip = 111111’ Inside the stored procedure generate dynamic sql statement and execute it with sp_executesql. Disadvantage: dynamic sql, sql injection Implement a stored procedure with multiple input parameters, representing the search fields from the UI, and use the following construction for selecting the records only for the requested fields in the where statement. WHERE (CustomerType = @CustomerType OR @CustomerType is null ) AND (CustomerZip = @CustomerZip OR @CustomerZip is null ) AND ………………………………………… Disadvantage: possible performance issue for the sql. 3.Implement separate stored procedure for each search parameter combinations. Disadvantage: The number of stored procedures will increase rapidly with the increase of the search parameters, repeated code.

    Read the article

  • C# how to get trailing spaces from the end of a varchar(513) field while exporting SQL table to a fl

    - by svon
    How do I get empty spaces from the end of a varchar(513) field while exporting data from SQL table to a flat file. I have a console application. Here is what I am using to export a SQL table having only one column of varchar(513) to a flat file. But I need to get all the 513 characters including spaces at the end. How do I change this code to incorporate that. Thanks { var destination = args[0]; var command = string.Format("Select * from {0}", Validator.Check(args[1])); var connectionstring = string.Format("Data Source={0}; Initial Catalog=dbname;Integrated Security=SSPI;", args[2]); var helper = new SqlHelper(command, CommandType.Text, connectionstring); using (StreamWriter writer = new StreamWriter(destination)) using (IDataReader reader = helper.ExecuteReader()) { while (reader.Read()) { Object[] values = new Object[reader.FieldCount]; int fieldCount = reader.GetValues(values); for (int i = 0; i < fieldCount; i++) writer.Write(values[i]); writer.WriteLine(); } writer.Close(); }

    Read the article

  • Toorcon14

    - by danx
    Toorcon 2012 Information Security Conference San Diego, CA, http://www.toorcon.org/ Dan Anderson, October 2012 It's almost Halloween, and we all know what that means—yes, of course, it's time for another Toorcon Conference! Toorcon is an annual conference for people interested in computer security. This includes the whole range of hackers, computer hobbyists, professionals, security consultants, press, law enforcement, prosecutors, FBI, etc. We're at Toorcon 14—see earlier blogs for some of the previous Toorcon's I've attended (back to 2003). This year's "con" was held at the Westin on Broadway in downtown San Diego, California. The following are not necessarily my views—I'm just the messenger—although I could have misquoted or misparaphrased the speakers. Also, I only reviewed some of the talks, below, which I attended and interested me. MalAndroid—the Crux of Android Infections, Aditya K. Sood Programming Weird Machines with ELF Metadata, Rebecca "bx" Shapiro Privacy at the Handset: New FCC Rules?, Valkyrie Hacking Measured Boot and UEFI, Dan Griffin You Can't Buy Security: Building the Open Source InfoSec Program, Boris Sverdlik What Journalists Want: The Investigative Reporters' Perspective on Hacking, Dave Maas & Jason Leopold Accessibility and Security, Anna Shubina Stop Patching, for Stronger PCI Compliance, Adam Brand McAfee Secure & Trustmarks — a Hacker's Best Friend, Jay James & Shane MacDougall MalAndroid—the Crux of Android Infections Aditya K. Sood, IOActive, Michigan State PhD candidate Aditya talked about Android smartphone malware. There's a lot of old Android software out there—over 50% Gingerbread (2.3.x)—and most have unpatched vulnerabilities. Of 9 Android vulnerabilities, 8 have known exploits (such as the old Gingerbread Global Object Table exploit). Android protection includes sandboxing, security scanner, app permissions, and screened Android app market. The Android permission checker has fine-grain resource control, policy enforcement. Android static analysis also includes a static analysis app checker (bouncer), and a vulnerablity checker. What security problems does Android have? User-centric security, which depends on the user to grant permission and make smart decisions. But users don't care or think about malware (the're not aware, not paranoid). All they want is functionality, extensibility, mobility Android had no "proper" encryption before Android 3.0 No built-in protection against social engineering and web tricks Alternative Android app markets are unsafe. Simply visiting some markets can infect Android Aditya classified Android Malware types as: Type A—Apps. These interact with the Android app framework. For example, a fake Netflix app. Or Android Gold Dream (game), which uploads user files stealthy manner to a remote location. Type K—Kernel. Exploits underlying Linux libraries or kernel Type H—Hybrid. These use multiple layers (app framework, libraries, kernel). These are most commonly used by Android botnets, which are popular with Chinese botnet authors What are the threats from Android malware? These incude leak info (contacts), banking fraud, corporate network attacks, malware advertising, malware "Hackivism" (the promotion of social causes. For example, promiting specific leaders of the Tunisian or Iranian revolutions. Android malware is frequently "masquerated". That is, repackaged inside a legit app with malware. To avoid detection, the hidden malware is not unwrapped until runtime. The malware payload can be hidden in, for example, PNG files. Less common are Android bootkits—there's not many around. What they do is hijack the Android init framework—alteering system programs and daemons, then deletes itself. For example, the DKF Bootkit (China). Android App Problems: no code signing! all self-signed native code execution permission sandbox — all or none alternate market places no robust Android malware detection at network level delayed patch process Programming Weird Machines with ELF Metadata Rebecca "bx" Shapiro, Dartmouth College, NH https://github.com/bx/elf-bf-tools @bxsays on twitter Definitions. "ELF" is an executable file format used in linking and loading executables (on UNIX/Linux-class machines). "Weird machine" uses undocumented computation sources (I think of them as unintended virtual machines). Some examples of "weird machines" are those that: return to weird location, does SQL injection, corrupts the heap. Bx then talked about using ELF metadata as (an uintended) "weird machine". Some ELF background: A compiler takes source code and generates a ELF object file (hello.o). A static linker makes an ELF executable from the object file. A runtime linker and loader takes ELF executable and loads and relocates it in memory. The ELF file has symbols to relocate functions and variables. ELF has two relocation tables—one at link time and another one at loading time: .rela.dyn (link time) and .dynsym (dynamic table). GOT: Global Offset Table of addresses for dynamically-linked functions. PLT: Procedure Linkage Tables—works with GOT. The memory layout of a process (not the ELF file) is, in order: program (+ heap), dynamic libraries, libc, ld.so, stack (which includes the dynamic table loaded into memory) For ELF, the "weird machine" is found and exploited in the loader. ELF can be crafted for executing viruses, by tricking runtime into executing interpreted "code" in the ELF symbol table. One can inject parasitic "code" without modifying the actual ELF code portions. Think of the ELF symbol table as an "assembly language" interpreter. It has these elements: instructions: Add, move, jump if not 0 (jnz) Think of symbol table entries as "registers" symbol table value is "contents" immediate values are constants direct values are addresses (e.g., 0xdeadbeef) move instruction: is a relocation table entry add instruction: relocation table "addend" entry jnz instruction: takes multiple relocation table entries The ELF weird machine exploits the loader by relocating relocation table entries. The loader will go on forever until told to stop. It stores state on stack at "end" and uses IFUNC table entries (containing function pointer address). The ELF weird machine, called "Brainfu*k" (BF) has: 8 instructions: pointer inc, dec, inc indirect, dec indirect, jump forward, jump backward, print. Three registers - 3 registers Bx showed example BF source code that implemented a Turing machine printing "hello, world". More interesting was the next demo, where bx modified ping. Ping runs suid as root, but quickly drops privilege. BF modified the loader to disable the library function call dropping privilege, so it remained as root. Then BF modified the ping -t argument to execute the -t filename as root. It's best to show what this modified ping does with an example: $ whoami bx $ ping localhost -t backdoor.sh # executes backdoor $ whoami root $ The modified code increased from 285948 bytes to 290209 bytes. A BF tool compiles "executable" by modifying the symbol table in an existing ELF executable. The tool modifies .dynsym and .rela.dyn table, but not code or data. Privacy at the Handset: New FCC Rules? "Valkyrie" (Christie Dudley, Santa Clara Law JD candidate) Valkyrie talked about mobile handset privacy. Some background: Senator Franken (also a comedian) became alarmed about CarrierIQ, where the carriers track their customers. Franken asked the FCC to find out what obligations carriers think they have to protect privacy. The carriers' response was that they are doing just fine with self-regulation—no worries! Carriers need to collect data, such as missed calls, to maintain network quality. But carriers also sell data for marketing. Verizon sells customer data and enables this with a narrow privacy policy (only 1 month to opt out, with difficulties). The data sold is not individually identifiable and is aggregated. But Verizon recommends, as an aggregation workaround to "recollate" data to other databases to identify customers indirectly. The FCC has regulated telephone privacy since 1934 and mobile network privacy since 2007. Also, the carriers say mobile phone privacy is a FTC responsibility (not FCC). FTC is trying to improve mobile app privacy, but FTC has no authority over carrier / customer relationships. As a side note, Apple iPhones are unique as carriers have extra control over iPhones they don't have with other smartphones. As a result iPhones may be more regulated. Who are the consumer advocates? Everyone knows EFF, but EPIC (Electrnic Privacy Info Center), although more obsecure, is more relevant. What to do? Carriers must be accountable. Opt-in and opt-out at any time. Carriers need incentive to grant users control for those who want it, by holding them liable and responsible for breeches on their clock. Location information should be added current CPNI privacy protection, and require "Pen/trap" judicial order to obtain (and would still be a lower standard than 4th Amendment). Politics are on a pro-privacy swing now, with many senators and the Whitehouse. There will probably be new regulation soon, and enforcement will be a problem, but consumers will still have some benefit. Hacking Measured Boot and UEFI Dan Griffin, JWSecure, Inc., Seattle, @JWSdan Dan talked about hacking measured UEFI boot. First some terms: UEFI is a boot technology that is replacing BIOS (has whitelisting and blacklisting). UEFI protects devices against rootkits. TPM - hardware security device to store hashs and hardware-protected keys "secure boot" can control at firmware level what boot images can boot "measured boot" OS feature that tracks hashes (from BIOS, boot loader, krnel, early drivers). "remote attestation" allows remote validation and control based on policy on a remote attestation server. Microsoft pushing TPM (Windows 8 required), but Google is not. Intel TianoCore is the only open source for UEFI. Dan has Measured Boot Tool at http://mbt.codeplex.com/ with a demo where you can also view TPM data. TPM support already on enterprise-class machines. UEFI Weaknesses. UEFI toolkits are evolving rapidly, but UEFI has weaknesses: assume user is an ally trust TPM implicitly, and attached to computer hibernate file is unprotected (disk encryption protects against this) protection migrating from hardware to firmware delays in patching and whitelist updates will UEFI really be adopted by the mainstream (smartphone hardware support, bank support, apathetic consumer support) You Can't Buy Security: Building the Open Source InfoSec Program Boris Sverdlik, ISDPodcast.com co-host Boris talked about problems typical with current security audits. "IT Security" is an oxymoron—IT exists to enable buiness, uptime, utilization, reporting, but don't care about security—IT has conflict of interest. There's no Magic Bullet ("blinky box"), no one-size-fits-all solution (e.g., Intrusion Detection Systems (IDSs)). Regulations don't make you secure. The cloud is not secure (because of shared data and admin access). Defense and pen testing is not sexy. Auditors are not solution (security not a checklist)—what's needed is experience and adaptability—need soft skills. Step 1: First thing is to Google and learn the company end-to-end before you start. Get to know the management team (not IT team), meet as many people as you can. Don't use arbitrary values such as CISSP scores. Quantitive risk assessment is a myth (e.g. AV*EF-SLE). Learn different Business Units, legal/regulatory obligations, learn the business and where the money is made, verify company is protected from script kiddies (easy), learn sensitive information (IP, internal use only), and start with low-hanging fruit (customer service reps and social engineering). Step 2: Policies. Keep policies short and relevant. Generic SANS "security" boilerplate policies don't make sense and are not followed. Focus on acceptable use, data usage, communications, physical security. Step 3: Implementation: keep it simple stupid. Open source, although useful, is not free (implementation cost). Access controls with authentication & authorization for local and remote access. MS Windows has it, otherwise use OpenLDAP, OpenIAM, etc. Application security Everyone tries to reinvent the wheel—use existing static analysis tools. Review high-risk apps and major revisions. Don't run different risk level apps on same system. Assume host/client compromised and use app-level security control. Network security VLAN != segregated because there's too many workarounds. Use explicit firwall rules, active and passive network monitoring (snort is free), disallow end user access to production environment, have a proxy instead of direct Internet access. Also, SSL certificates are not good two-factor auth and SSL does not mean "safe." Operational Controls Have change, patch, asset, & vulnerability management (OSSI is free). For change management, always review code before pushing to production For logging, have centralized security logging for business-critical systems, separate security logging from administrative/IT logging, and lock down log (as it has everything). Monitor with OSSIM (open source). Use intrusion detection, but not just to fulfill a checkbox: build rules from a whitelist perspective (snort). OSSEC has 95% of what you need. Vulnerability management is a QA function when done right: OpenVas and Seccubus are free. Security awareness The reality is users will always click everything. Build real awareness, not compliance driven checkbox, and have it integrated into the culture. Pen test by crowd sourcing—test with logging COSSP http://www.cossp.org/ - Comprehensive Open Source Security Project What Journalists Want: The Investigative Reporters' Perspective on Hacking Dave Maas, San Diego CityBeat Jason Leopold, Truthout.org The difference between hackers and investigative journalists: For hackers, the motivation varies, but method is same, technological specialties. For investigative journalists, it's about one thing—The Story, and they need broad info-gathering skills. J-School in 60 Seconds: Generic formula: Person or issue of pubic interest, new info, or angle. Generic criteria: proximity, prominence, timeliness, human interest, oddity, or consequence. Media awareness of hackers and trends: journalists becoming extremely aware of hackers with congressional debates (privacy, data breaches), demand for data-mining Journalists, use of coding and web development for Journalists, and Journalists busted for hacking (Murdock). Info gathering by investigative journalists include Public records laws. Federal Freedom of Information Act (FOIA) is good, but slow. California Public Records Act is a lot stronger. FOIA takes forever because of foot-dragging—it helps to be specific. Often need to sue (especially FBI). CPRA is faster, and requests can be vague. Dumps and leaks (a la Wikileaks) Journalists want: leads, protecting ourselves, our sources, and adapting tools for news gathering (Google hacking). Anonomity is important to whistleblowers. They want no digital footprint left behind (e.g., email, web log). They don't trust encryption, want to feel safe and secure. Whistleblower laws are very weak—there's no upside for whistleblowers—they have to be very passionate to do it. Accessibility and Security or: How I Learned to Stop Worrying and Love the Halting Problem Anna Shubina, Dartmouth College Anna talked about how accessibility and security are related. Accessibility of digital content (not real world accessibility). mostly refers to blind users and screenreaders, for our purpose. Accessibility is about parsing documents, as are many security issues. "Rich" executable content causes accessibility to fail, and often causes security to fail. For example MS Word has executable format—it's not a document exchange format—more dangerous than PDF or HTML. Accessibility is often the first and maybe only sanity check with parsing. They have no choice because someone may want to read what you write. Google, for example, is very particular about web browser you use and are bad at supporting other browsers. Uses JavaScript instead of links, often requiring mouseover to display content. PDF is a security nightmare. Executible format, embedded flash, JavaScript, etc. 15 million lines of code. Google Chrome doesn't handle PDF correctly, causing several security bugs. PDF has an accessibility checker and PDF tagging, to help with accessibility. But no PDF checker checks for incorrect tags, untagged content, or validates lists or tables. None check executable content at all. The "Halting Problem" is: can one decide whether a program will ever stop? The answer, in general, is no (Rice's theorem). The same holds true for accessibility checkers. Language-theoretic Security says complicated data formats are hard to parse and cannot be solved due to the Halting Problem. W3C Web Accessibility Guidelines: "Perceivable, Operable, Understandable, Robust" Not much help though, except for "Robust", but here's some gems: * all information should be parsable (paraphrasing) * if not parsable, cannot be converted to alternate formats * maximize compatibility in new document formats Executible webpages are bad for security and accessibility. They say it's for a better web experience. But is it necessary to stuff web pages with JavaScript for a better experience? A good example is The Drudge Report—it has hand-written HTML with no JavaScript, yet drives a lot of web traffic due to good content. A bad example is Google News—hidden scrollbars, guessing user input. Solutions: Accessibility and security problems come from same source Expose "better user experience" myth Keep your corner of Internet parsable Remember "Halting Problem"—recognize false solutions (checking and verifying tools) Stop Patching, for Stronger PCI Compliance Adam Brand, protiviti @adamrbrand, http://www.picfun.com/ Adam talked about PCI compliance for retail sales. Take an example: for PCI compliance, 50% of Brian's time (a IT guy), 960 hours/year was spent patching POSs in 850 restaurants. Often applying some patches make no sense (like fixing a browser vulnerability on a server). "Scanner worship" is overuse of vulnerability scanners—it gives a warm and fuzzy and it's simple (red or green results—fix reds). Scanners give a false sense of security. In reality, breeches from missing patches are uncommon—more common problems are: default passwords, cleartext authentication, misconfiguration (firewall ports open). Patching Myths: Myth 1: install within 30 days of patch release (but PCI §6.1 allows a "risk-based approach" instead). Myth 2: vendor decides what's critical (also PCI §6.1). But §6.2 requires user ranking of vulnerabilities instead. Myth 3: scan and rescan until it passes. But PCI §11.2.1b says this applies only to high-risk vulnerabilities. Adam says good recommendations come from NIST 800-40. Instead use sane patching and focus on what's really important. From NIST 800-40: Proactive: Use a proactive vulnerability management process: use change control, configuration management, monitor file integrity. Monitor: start with NVD and other vulnerability alerts, not scanner results. Evaluate: public-facing system? workstation? internal server? (risk rank) Decide:on action and timeline Test: pre-test patches (stability, functionality, rollback) for change control Install: notify, change control, tickets McAfee Secure & Trustmarks — a Hacker's Best Friend Jay James, Shane MacDougall, Tactical Intelligence Inc., Canada "McAfee Secure Trustmark" is a website seal marketed by McAfee. A website gets this badge if they pass their remote scanning. The problem is a removal of trustmarks act as flags that you're vulnerable. Easy to view status change by viewing McAfee list on website or on Google. "Secure TrustGuard" is similar to McAfee. Jay and Shane wrote Perl scripts to gather sites from McAfee and search engines. If their certification image changes to a 1x1 pixel image, then they are longer certified. Their scripts take deltas of scans to see what changed daily. The bottom line is change in TrustGuard status is a flag for hackers to attack your site. Entire idea of seals is silly—you're raising a flag saying if you're vulnerable.

    Read the article

  • setCurrentRowWithKey and setCurrentRowWithKeyValue

    - by raghu.yadav
    Good demo demo by shay by shay on how to use setCurrentRowWithKeyValue to synchronize the master and details of same table. Example : Employee master table list and EmployeeDetail form to edit records. However there are many ways to achieve the same usecase. You can use the clicktoEdit property on table row to edit records in place within in table and there is also a feature to show more for few columns in table tools. But objective here is to see how and where and all we can make use of setCurrentRowWithKey and setCurrentRowWithKeyValue. Here is the link about this explains how we can make use of this. link more to be added.

    Read the article

  • Sun Fire X4800 M2 Delivers World Record TPC-C for x86 Systems

    - by Brian
    Oracle's Sun Fire X4800 M2 server equipped with eight 2.4 GHz Intel Xeon Processor E7-8870 chips obtained a result of 5,055,888 tpmC on the TPC-C benchmark. This result is a world record for x86 servers. Oracle demonstrated this world record database performance running Oracle Database 11g Release 2 Enterprise Edition with Partitioning. The Sun Fire X4800 M2 server delivered a new x86 TPC-C world record of 5,055,888 tpmC with a price performance of $0.89/tpmC using Oracle Database 11g Release 2. This configuration is available 06/26/12. The Sun Fire X4800 M2 server delivers 3.0x times better performance than the next 8-processor result, an IBM System p 570 equipped with POWER6 processors. The Sun Fire X4800 M2 server has 3.1x times better price/performance than the 8-processor 4.7GHz POWER6 IBM System p 570. The Sun Fire X4800 M2 server has 1.6x times better performance than the 4-processor IBM x3850 X5 system equipped with Intel Xeon processors. This is the first TPC-C result on any system using eight Intel Xeon Processor E7-8800 Series chips. The Sun Fire X4800 M2 server is the first x86 system to get over 5 million tpmC. The Oracle solution utilized Oracle Linux operating system and Oracle Database 11g Enterprise Edition Release 2 with Partitioning to produce the x86 world record TPC-C benchmark performance. Performance Landscape Select TPC-C results (sorted by tpmC, bigger is better) System p/c/t tpmC Price/tpmC Avail Database MemorySize Sun Fire X4800 M2 8/80/160 5,055,888 0.89 USD 6/26/2012 Oracle 11g R2 4 TB IBM x3850 X5 4/40/80 3,014,684 0.59 USD 7/11/2011 DB2 ESE 9.7 3 TB IBM x3850 X5 4/32/64 2,308,099 0.60 USD 5/20/2011 DB2 ESE 9.7 1.5 TB IBM System p 570 8/16/32 1,616,162 3.54 USD 11/21/2007 DB2 9.0 2 TB p/c/t - processors, cores, threads Avail - availability date Oracle and IBM TPC-C Response times System tpmC Response Time (sec) New Order 90th% Response Time (sec) New Order Average Sun Fire X4800 M2 5,055,888 0.210 0.166 IBM x3850 X5 3,014,684 0.500 0.272 Ratios - Oracle Better 1.6x 1.4x 1.3x Oracle uses average new order response time for comparison between Oracle and IBM. Graphs of Oracle's and IBM's response times for New-Order can be found in the full disclosure reports on TPC's website TPC-C Official Result Page. Configuration Summary and Results Hardware Configuration: Server Sun Fire X4800 M2 server 8 x 2.4 GHz Intel Xeon Processor E7-8870 4 TB memory 8 x 300 GB 10K RPM SAS internal disks 8 x Dual port 8 Gbs FC HBA Data Storage 10 x Sun Fire X4270 M2 servers configured as COMSTAR heads, each with 1 x 3.06 GHz Intel Xeon X5675 processor 8 GB memory 10 x 2 TB 7.2K RPM 3.5" SAS disks 2 x Sun Storage F5100 Flash Array storage (1.92 TB each) 1 x Brocade 5300 switches Redo Storage 2 x Sun Fire X4270 M2 servers configured as COMSTAR heads, each with 1 x 3.06 GHz Intel Xeon X5675 processor 8 GB memory 11 x 2 TB 7.2K RPM 3.5" SAS disks Clients 8 x Sun Fire X4170 M2 servers, each with 2 x 3.06 GHz Intel Xeon X5675 processors 48 GB memory 2 x 300 GB 10K RPM SAS disks Software Configuration: Oracle Linux (Sun Fire 4800 M2) Oracle Solaris 11 Express (COMSTAR for Sun Fire X4270 M2) Oracle Solaris 10 9/10 (Sun Fire X4170 M2) Oracle Database 11g Release 2 Enterprise Edition with Partitioning Oracle iPlanet Web Server 7.0 U5 Tuxedo CFS-R Tier 1 Results: System: Sun Fire X4800 M2 tpmC: 5,055,888 Price/tpmC: 0.89 USD Available: 6/26/2012 Database: Oracle Database 11g Cluster: no New Order Average Response: 0.166 seconds Benchmark Description TPC-C is an OLTP system benchmark. It simulates a complete environment where a population of terminal operators executes transactions against a database. The benchmark is centered around the principal activities (transactions) of an order-entry environment. These transactions include entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at the warehouses. Key Points and Best Practices Oracle Database 11g Release 2 Enterprise Edition with Partitioning scales easily to this high level of performance. COMSTAR (Common Multiprotocol SCSI Target) is the software framework that enables an Oracle Solaris host to serve as a SCSI Target platform. COMSTAR uses a modular approach to break the huge task of handling all the different pieces in a SCSI target subsystem into independent functional modules which are glued together by the SCSI Target Mode Framework (STMF). The modules implementing functionality at SCSI level (disk, tape, medium changer etc.) are not required to know about the underlying transport. And the modules implementing the transport protocol (FC, iSCSI, etc.) are not aware of the SCSI-level functionality of the packets they are transporting. The framework hides the details of allocation providing execution context and cleanup of SCSI commands and associated resources and simplifies the task of writing the SCSI or transport modules. Oracle iPlanet Web Server middleware is used for the client tier of the benchmark. Each web server instance supports more than a quarter-million users while satisfying the response time requirement from the TPC-C benchmark. See Also Oracle Press Release -- Sun Fire X4800 M2 TPC-C Executive Summary tpc.org Complete Sun Fire X4800 M2 TPC-C Full Disclosure Report tpc.org Transaction Processing Performance Council (TPC) Home Page Ideas International Benchmark Page Sun Fire X4800 M2 Server oracle.com OTN Oracle Linux oracle.com OTN Oracle Solaris oracle.com OTN Oracle Database 11g Release 2 Enterprise Edition oracle.com OTN Sun Storage F5100 Flash Array oracle.com OTN Disclosure Statement TPC Benchmark C, tpmC, and TPC-C are trademarks of the Transaction Processing Performance Council (TPC). Sun Fire X4800 M2 (8/80/160) with Oracle Database 11g Release 2 Enterprise Edition with Partitioning, 5,055,888 tpmC, $0.89 USD/tpmC, available 6/26/2012. IBM x3850 X5 (4/40/80) with DB2 ESE 9.7, 3,014,684 tpmC, $0.59 USD/tpmC, available 7/11/2011. IBM x3850 X5 (4/32/64) with DB2 ESE 9.7, 2,308,099 tpmC, $0.60 USD/tpmC, available 5/20/2011. IBM System p 570 (8/16/32) with DB2 9.0, 1,616,162 tpmC, $3.54 USD/tpmC, available 11/21/2007. Source: http://www.tpc.org/tpcc, results as of 7/15/2011.

    Read the article

  • Partition Wise Joins

    - by jean-pierre.dijcks
    Some say they are the holy grail of parallel computing and PWJ is the basis for a shared nothing system and the only join method that is available on a shared nothing system (yes this is oversimplified!). The magic in Oracle is of course that is one of many ways to join data. And yes, this is the old flexibility vs. simplicity discussion all over, so I won't go there... the point is that what you must do in a shared nothing system, you can do in Oracle with the same speed and methods. The Theory A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme. In shared nothing this is effectively hard partitioning locating data on a specific node / storage combo. In Oracle is is logical partitioning. If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets. PWJ's in Oracle Since we do not hard partition the data across nodes in Oracle we use the Partitioning option to the database to create the buckets, then set the Degree of Parallelism (or run Auto DOP - see here) and get our PWJs. The main questions always asked are: How many partitions should I create? What should my DOP be? In a shared nothing system the answer is of course, as many partitions as there are nodes which will be your DOP. In Oracle we do want you to look at the workload and concurrency, and once you know that to understand the following rules of thumb. Within Oracle we have more ways of joining of data, so it is important to understand some of the PWJ ideas and what it means if you have an uneven distribution across processes. Assume we have a simple scenario where we partition the data on a hash key resulting in 4 hash partitions (H1 -H4). We have 2 parallel processes that have been tasked with reading these partitions (P1 - P2). The work is evenly divided assuming the partitions are the same size and we can scan this in time t1 as shown below. Now assume that we have changed the system and have a 5th partition but still have our 2 workers P1 and P2. The time it takes is actually 50% more assuming the 5th partition has the same size as the original H1 - H4 partitions. In other words to scan these 5 partitions, the time t2 it takes is not 1/5th more expensive, it is a lot more expensive and some other join plans may now start to look exciting to the optimizer. Just to post the disclaimer, it is not as simple as I state it here, but you get the idea on how much more expensive this plan may now look... Based on this little example there are a few rules of thumb to follow to get the partition wise joins. First, choose a DOP that is a factor of two (2). So always choose something like 2, 4, 8, 16, 32 and so on... Second, choose a number of partitions that is larger or equal to 2* DOP. Third, make sure the number of partitions is divisible through 2 without orphans. This is also known as an even number... Fourth, choose a stable partition count strategy, which is typically hash, which can be a sub partitioning strategy rather than the main strategy (range - hash is a popular one). Fifth, make sure you do this on the join key between the two large tables you want to join (and this should be the obvious one...). Translating this into an example: DOP = 8 (determined based on concurrency or by using Auto DOP with a cap due to concurrency) says that the number of partitions >= 16. Number of hash (sub) partitions = 32, which gives each process four partitions to work on. This number is somewhat arbitrary and depends on your data and system. In this case my main reasoning is that if you get more room on the box you can easily move the DOP for the query to 16 without repartitioning... and of course it makes for no leftovers on the table... And yes, we recommend up-to-date statistics. And before you start complaining, do read this post on a cool way to do stats in 11.

    Read the article

  • SQL SERVER – Fundamentals of Columnstore Index

    - by pinaldave
    There are two kind of storage in database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data is relevant or not, column store queries need only to search much lesser number of the columns. This means major increases in search speed and hard drive use. Additionally, the column store indexes are heavily compressed, which translates to even greater memory and faster searches. I am sure this looks very exciting and it does not mean that you convert every single index from row store to column store index. One has to understand the proper places where to use row store or column store indexes. Let us understand in this article what is the difference in Columnstore type of index. Column store indexes are run by Microsoft’s VertiPaq technology. However, all you really need to know is that this method of storing data is columns on a single page is much faster and more efficient. Creating a column store index is very easy, and you don’t have to learn new syntax to create them. You just need to specify the keyword “COLUMNSTORE” and enter the data as you normally would. Keep in mind that once you add a column store to a table, though, you cannot delete, insert or update the data – it is READ ONLY. However, since column store will be mainly used for data warehousing, this should not be a big problem. You can always use partitioning to avoid rebuilding the index. A columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. The difference between column store and row store approaches is illustrated below: In case of the row store indexes multiple pages will contain multiple rows of the columns spanning across multiple pages. In case of column store indexes multiple pages will contain multiple single columns. This will lead only the columns needed to solve a query will be fetched from disk. Additionally there is good chance that there will be redundant data in a single column which will further help to compress the data, this will have positive effect on buffer hit rate as most of the data will be in memory and due to same it will not need to be retrieved. Let us see small example of how columnstore index improves the performance of the query on a large table. As a first step let us create databaseset which is large enough to show performance impact of columnstore index. The time taken to create sample database may vary on different computer based on the resources. USE AdventureWorks GO -- Create New Table CREATE TABLE [dbo].[MySalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] GO -- Create clustered index CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ( [SalesOrderDetailID]) GO -- Create Sample Data Table -- WARNING: This Query may run upto 2-10 minutes based on your systems resources INSERT INTO [dbo].[MySalesOrderDetail] SELECT S1.* FROM Sales.SalesOrderDetail S1 GO 100 Now let us do quick performance test. I have kept STATISTICS IO ON for measuring how much IO following queries take. In my test first I will run query which will use regular index. We will note the IO usage of the query. After that we will create columnstore index and will measure the IO of the same. -- Performance Test -- Comparing Regular Index with ColumnStore Index USE AdventureWorks GO SET STATISTICS IO ON GO -- Select Table with regular Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GO -- Table 'MySalesOrderDetail'. Scan count 1, logical reads 342261, physical reads 0, read-ahead reads 0. -- Create ColumnStore Index CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID) GO -- Select Table with Columnstore Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GO It is very clear from the results that query is performance extremely fast after creating ColumnStore Index. The amount of the pages it has to read to run query is drastically reduced as the column which are needed in the query are stored in the same page and query does not have to go through every single page to read those columns. If we enable execution plan and compare we can see that column store index performance way better than regular index in this case. Let us clean up the database. -- Cleanup DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail] GO TRUNCATE TABLE dbo.MySalesOrderDetail GO DROP TABLE dbo.MySalesOrderDetail GO In future posts we will see cases where Columnstore index is not appropriate solution as well few other tricks and tips of the columnstore index. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • Sorting and Filtering By Model-Based LOV Display Value

    - by Steven Davelaar
    If you use a model-based LOV and you use display type "choice", then ADF nicely displays the display value, even if the table is read-only. In the screen shot below, you see the RegionName attribute displayed instead of the RegionId. This is accomplished by the model-based LOV, I did not modify the Countries view object to include a join with Regions.  Also note the sort icon, the table is sorted by RegionId. This sorting typically results in a bug reported by your test team. Europe really shouldn't come before America when sorting ascending, right? To fix this, we could of course change the Countries view object query and add a join with the Regions table to include the RegionName attribute. If the table is updateable, we still need the choice list, so we need to move the model-based LOV from the RegionId attribute to the RegionName attribute and hide the RegionId attribute in the table. But that is a lot of work for such a simple requirement, in particular if we have lots of model-based choice lists in our view object. Fortunately, there is an easier way to do this, with some generic code in your view object base class that fixes this at once for all model-based choice lists that we have defined in our application. The trick is to override the method getSortCriteria() in the base view object class. By default, this method returns null because the sorting is done in the database through a SQL Order By clause. However, if the getSortCriteria method does return a sort criteria the framework will perform in memory sorting which is what we need to achieve sorting by region name. So, inside this method we need to evaluate the Order By clause, and if the order by column matches an attribute that has a model-based LOV choicelist defined with a display attribute that is different from the value attribute, we need to return a sort criterria. Here is the complete code of this method: public SortCriteria[] getSortCriteria() {   String orderBy = getOrderByClause();          if (orderBy!=null )   {     boolean descending = false;     if (orderBy.endsWith(" DESC"))      {       descending = true;       orderBy = orderBy.substring(0,orderBy.length()-5);     }     // extract column name, is part after the dot     int dotpos = orderBy.lastIndexOf(".");     String columnName = orderBy.substring(dotpos+1);     // loop over attributes and find matching attribute     AttributeDef orderByAttrDef = null;     for (AttributeDef attrDef : getAttributeDefs())     {       if (columnName.equals(attrDef.getColumnName()))       {         orderByAttrDef = attrDef;         break;       }     }     if (orderByAttrDef!=null && "choice".equals(orderByAttrDef.getProperty("CONTROLTYPE"))          && orderByAttrDef.getListBindingDef()!=null)     {       String orderbyAttr = orderByAttrDef.getName();       String[] displayAttrs = orderByAttrDef.getListBindingDef().getListDisplayAttrNames();       String[] listAttrs = orderByAttrDef.getListBindingDef().getListAttrNames();       // if first list display attributes is not the same as first list attribute, than the value       // displayed is different from the value copied back to the order by attribute, in which case we need to       // use our custom comparator       if (displayAttrs!=null && listAttrs!=null && displayAttrs.length>0 && !displayAttrs[0].equals(listAttrs[0]))       {                  SortCriteriaImpl sc1 = new SortCriteriaImpl(orderbyAttr, descending);         SortCriteria[] sc = new SortCriteriaImpl[]{sc1};         return sc;                           }     }     }   return super.getSortCriteria(); } If this method returns a sort criteria, then the framework will call the sort method on the view object. The sort method uses a Comparator object to determine the sequence in which the rows should be returned. This comparator is retrieved by calling the getRowComparator method on the view object. So, to ensure sorting by our display value, we need to override this method to return our custom comparator: public Comparator getRowComparator() {   return new LovDisplayAttributeRowComparator(getSortCriteria()); } The custom comparator class extends the default RowComparator class and overrides the method compareRows and looks up the choice display value to compare the two rows. The complete code of this class is included in the sample application.  With this code in place, clicking on the Region sort icon nicely sorts the countries by RegionName, as you can see below. When using the Query-By-Example table filter at the top of the table, you typically want to use the same choice list to filter the rows. One way to do that is documented in ADF code corner sample 16 - How To Customize the ADF Faces Table Filter.The solution in this sample is perfectly fine to use. This sample requires you to define a separate iterator binding and associated tree binding to populate the choice list in the table filter area using the af:iterator tag. You might be able to reuse the same LOV view object instance in this iterator binding that is used as view accessor for the model-bassed LOV. However, I have seen quite a few customers who have a generic LOV view object (mapped to one "refcodes" table) with the bind variable values set in the LOV view accessor. In such a scenario, some duplicate work is needed to get a dedicated view object instance with the correct bind variables that can be used in the iterator binding. Looking for ways to maximize reuse, wouldn't it be nice if we could just reuse our model-based LOV to populate this filter choice list? Well we can. Here are the basic steps: 1. Create an attribute list binding in the page definition that we can use to retrieve the list of SelectItems needed to populate the choice list <list StaticList="false" Uses="LOV_RegionId"               IterBinding="CountriesView1Iterator" id="RegionId"/>  We need this "current row" list binding because the implicit list binding used by the item in the table is not accessible outside a table row, we cannot use the expression #{row.bindings.RegionId} in the table filter facet. 2. Create a Map-style managed bean with the get method retrieving the list binding as key, and returning the list of SelectItems. To return this list, we take the list of selectItems contained by the list binding and replace the index number that is normally used as key value with the actual attribute value that is set by the choice list. Here is the code of the get method:  public Object get(Object key) {   if (key instanceof FacesCtrlListBinding)   {     // we need to cast to internal class FacesCtrlListBinding rather than JUCtrlListBinding to     // be able to call getItems method. To prevent this import, we could evaluate an EL expression     // to get the list of items     FacesCtrlListBinding lb = (FacesCtrlListBinding) key;     if (cachedFilterLists.containsKey(lb.getName()))     {       return cachedFilterLists.get(lb.getName());     }     List<SelectItem> items = (List<SelectItem>)lb.getItems();     if (items==null || items.size()==0)     {       return items;     }     List<SelectItem> newItems = new ArrayList<SelectItem>();     JUCtrlValueDef def = ((JUCtrlValueDef)lb.getDef());     String valueAttr = def.getFirstAttrName();     // the items list has an index number as value, we need to replace this with the actual     // value of the attribute that is copied back by the choice list     for (int i = 0; i < items.size(); i++)     {       SelectItem si = (SelectItem) items.get(i);       Object value = lb.getValueFromList(i);       if (value instanceof Row)       {         Row row = (Row) value;         si.setValue(row.getAttribute(valueAttr));                 }       else       {         // this is the "empty" row, set value to empty string so all rows will be returned         // as user no longer wants to filter on this attribute         si.setValue("");       }       newItems.add(si);     }     cachedFilterLists.put(lb.getName(), newItems);     return newItems;   }   return null; } Note that we added caching to speed up performance, and to handle the situation where table filters or search criteria are set such that no rows are retrieved in the table. When there are no rows, there is no current row and the getItems method on the list binding will return no items.  An alternative approach to create the list of SelectItems would be to retrieve the iterator binding from the list binding and loop over the rows in the iterator binding rowset. Then we wouldn't need the import of the ADF internal oracle.adfinternal.view.faces.model.binding.FacesCtrlListBinding class, but then we need to figure out the display attributes from the list binding definition, and possible separate them with a dash if multiple display attributes are defined in the LOV. Doable but less reuse and more work. 3. Inside the filter facet for the column create an af:selectOneChoice with the value property of the f:selectItems tag referencing the get method of the managed bean:  <f:facet name="filter">   <af:selectOneChoice id="soc0" autoSubmit="true"                       value="#{vs.filterCriteria.RegionId}">     <!-- attention: the RegionId list binding must be created manually in the page definition! -->                       <f:selectItems id="si0"                    value="#{viewScope.TableFilterChoiceList[bindings.RegionId]}"/>   </af:selectOneChoice> </f:facet> Note that the managed bean is defined in viewScope for the caching to take effect. Here is a screen shot of the tabe filter in action: You can download the sample application here. 

    Read the article

< Previous Page | 217 218 219 220 221 222 223 224 225 226 227 228  | Next Page >